Hi All,
I have written a function that moves data from a load table
(data_transfer.tbl_sales_order_line_item) to two data tables
(sales_order.tbl_line_item & sales_order.tbl_item_description) the second of
which is dependent on the first. When I run the function manually (create it
as a function returning an integer and execute it after loading the data witht
the COPY command) it works fine. When I convert it to a TRIGGER and COPY data
to the load table I get an error that says a foreign key constraint is being
violated. I do not understand why this happens and more importantly how to
fix it. I would appreciate any help that you may provide. The sql (lots of
it) to create the tables and the functions is below. The data file is also below.
This is the command that I am using and the error that I get:
# cat 12448.tbl_line_item.txt | psql --echo-all --dbname IPADB --username
postgres --command "COPY data_transfer.tbl_sales_order_line_item FROM stdin
WITH DELIMITER AS ',' NULL AS '';"
COPY data_transfer.tbl_sales_order_line_item FROM stdin WITH DELIMITER AS ','
NULL AS '';
ERROR: tbl_item_description_fkey1 referential integrity violation - key
referenced from tbl_item_description not found in tbl_line_item
lost synchronization with server, resetting connection
Kind Regards,
Keith
-- Table: data_transfer.tbl_sales_order_line_item
CREATE TABLE data_transfer.tbl_sales_order_line_item
(
so_number int4 NOT NULL,
so_line int2 NOT NULL,
quantity float4,
item_id varchar(20),
description varchar(160),
unit_price numeric DEFAULT 0,
extended_price numeric DEFAULT 0,
CONSTRAINT tbl_sales_order_line_item_pkey PRIMARY KEY (so_number, so_line)
) WITHOUT OIDS;
-- Table: sales_order.tbl_line_item
CREATE TABLE sales_order.tbl_line_item
(
number int4 NOT NULL,
line int2 NOT NULL,
quantity float4 NOT NULL,
item_id varchar(20) NOT NULL,
unit_price numeric,
po_number int4,
CONSTRAINT tbl_line_item_pkey PRIMARY KEY (number, line),
) WITHOUT OIDS;
-- Table: sales_order.tbl_item_description
CREATE TABLE sales_order.tbl_item_description
(
number int4 NOT NULL,
line int2 NOT NULL,
description varchar(160) NOT NULL,
item_tag varchar(64),
CONSTRAINT tbl_net_description_pkey PRIMARY KEY (number, line),
CONSTRAINT tbl_item_description_fkey1 FOREIGN KEY (number, line) REFERENCES
sales_order.tbl_line_item (number, line) ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT OIDS;
-- Table: peachtree.tbl_item
CREATE TABLE peachtree.tbl_item
(
id varchar(20) NOT NULL,
description varchar(30) NOT NULL,
item_class int2 NOT NULL,
inactive bool NOT NULL,
sales_description varchar(160),
purchase_description varchar(160),
last_unit_cost float4 NOT NULL,
costing_method int2 NOT NULL,
sales_gl_account varchar(15),
inventory_gl_account varchar(15),
cogs_gl_account varchar(15),
item_type varchar(8),
unit_of_measure varchar(6),
weight float4,
reorder_point float4,
reorder_quantity float4,
purchase_uom varchar(6),
ptos_uom_factor float4 DEFAULT 1,
CONSTRAINT tbl_part_pkey PRIMARY KEY (id),
) WITHOUT OIDS;
-- Function: data_transfer.tf_xfr_sales_order_line_item_data()
CREATE OR REPLACE FUNCTION data_transfer.tf_xfr_sales_order_line_item_data()
RETURNS trigger AS
'
DECLARE
rcrd_line RECORD;
BEGIN
-- Retrieve all of the sales orders. Grab the item type for later use.
FOR rcrd_line IN SELECT data_transfer.tbl_sales_order_line_item.so_number,
data_transfer.tbl_sales_order_line_item.so_line,
data_transfer.tbl_sales_order_line_item.quantity,
data_transfer.tbl_sales_order_line_item.item_id,
peachtree.tbl_item.item_type,
data_transfer.tbl_sales_order_line_item.description,
data_transfer.tbl_sales_order_line_item.unit_price,
data_transfer.tbl_sales_order_line_item.extended_price
FROM data_transfer.tbl_sales_order_line_item
JOIN peachtree.tbl_item
ON (
data_transfer.tbl_sales_order_line_item.item_id = peachtree.tbl_item.id )
ORDER BY
data_transfer.tbl_sales_order_line_item.so_number,
data_transfer.tbl_sales_order_line_item.so_line
LOOP
-- Attempt to retrieve a matching record from the target table.
PERFORM sales_order.tbl_line_item.number
FROM sales_order.tbl_line_item
WHERE sales_order.tbl_line_item.number = rcrd_line.so_number;
IF FOUND THEN
-- Delete the data from the dependent tables for the matching record
that was found.
-- comment this DELETE
-- out to see FROM sales_order.tbl_item_net_panel
-- if cascade WHERE sales_order.tbl_item_net_panel.number =
rcrd_line.so_number;
-- delete works DELETE
-- comment this FROM sales_order.tbl_item_net_production
-- out to see WHERE sales_order.tbl_item_net_production.number =
rcrd_line.so_number;
-- if cascade DELETE
-- delete works FROM sales_order.tbl_item_bom
-- comment this WHERE sales_order.tbl_item_bom.number =
rcrd_line.so_number;
-- out to see DELETE
-- if cascade FROM sales_order.tbl_item_description
-- delete works WHERE sales_order.tbl_item_description.number =
rcrd_line.so_number;
DELETE
FROM sales_order.tbl_line_item
WHERE sales_order.tbl_line_item.number = rcrd_line.so_number;
END IF;
-- Insert the detail information into the line item table.
INSERT INTO sales_order.tbl_line_item
( number,
line,
quantity,
item_id,
unit_price )
VALUES ( rcrd_line.so_number,
rcrd_line.so_line,
rcrd_line.quantity,
rcrd_line.item_id,
rcrd_line.unit_price );
-- The description needs to be stored if the item type is not ASY | DIR.
IF rcrd_line.item_type <> \'ASY\' AND
rcrd_line.item_type <> \'DIR\' THEN
INSERT INTO sales_order.tbl_item_description
( number,
line,
description )
VALUES ( rcrd_line.so_number,
rcrd_line.so_line,
rcrd_line.description );
END IF;
-- The record has been processed. Remove it from the transfer table.
DELETE
FROM data_transfer.tbl_sales_order_line_item
WHERE data_transfer.tbl_sales_order_line_item.so_number =
rcrd_line.so_number;
END LOOP;
RETURN NULL;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
-- Trigger: tgr_xfr_sales_order_line_item_data on
data_transfer.tbl_sales_order_line_item
CREATE TRIGGER tgr_xfr_sales_order_line_item_data
AFTER INSERT
ON data_transfer.tbl_sales_order_line_item
FOR EACH ROW
EXECUTE PROCEDURE data_transfer.tf_xfr_sales_order_line_item_data();
INSERT INTO tbl_item VALUES ('M3000OR', 'MODEL 3000 HTPP 4in sq. mesh ,', 1,
true, 'M-3000 HTPP ORANGE 4in sq. mesh , Border: Size:', '218-100-03
BRILLIANT Orange, (Mark M-3000OR) PP Netting, 4.75mm, 10.1 x 198.9M, 100mm
sq', 2886.08, 0, '40000', '50000', '50000', 'PREIPA', 'EA', 0, 0, 0, NULL, NULL);
INSERT INTO tbl_item VALUES ('TIE20', 'Nylon Ties 24in', 1, true, '24in Nylon
Tie 175#', '#B24HOL 175#, 24in Black Nylon UV Cable Tie 13.00/C', 0.14, 0,
'40110', '50110', '50110', 'PREIPA', 'EA', 0, 0, 0, NULL, NULL);
INSERT INTO tbl_item VALUES ('CAB2-1000', '1/4in Galv. Cable - 1000ft Spo', 1,
true, '1/4in Galv Cable - 1000ft Spool', '1/4in Galv Cable 7x19, 1000ft
Spool', 120, 1, '40110', '50110', '50110', 'PREIPA', 'EA', 0, 0, 0, NULL, NULL);
INSERT INTO tbl_item VALUES ('CC2', '1/4in Galv. Cable Clamps', 1, true,
'1/4in Galv Cable Clamps', '1/4in Galv. Wire Rope Clips', 0.13, 0, '40110',
'50110', '50110', 'PREIPA', 'EA', 0, 0, 0, NULL, NULL);
INSERT INTO tbl_item VALUES ('TM2', '1/4in Galv. Thimble', 1, true, '1/4in
Galv Thimble', '1/4in Galv. Thimble', 0.06, 0, '40110', '50110', '50110',
'PREIPA', 'EA', 0, 0, 0, NULL, NULL);
INSERT INTO tbl_item VALUES ('EB36', '3/8in x 6in Galv. Nut Eye Bolt', 1,
true, '3/8in x 6in Galv Nut/Eyebolt with (1) HN38, (1) LW38', '3/8in x 6in
Galv. Nut Eye Bolt', 0.89, 0, '40110', '50110', '50110', 'PREIPA', 'EA', 0, 0,
0, NULL, NULL);
INSERT INTO tbl_item VALUES ('IC', 'GALV ICE CLIP', 1, true, 'Galv ICE Fast
Clip', 'Item 95-8-14 Packaged 50/bag Priced $68.00/1000', 0.065, 0, '40110',
'50110', '50110', 'PREIPA', 'EA', 0, 0, 0, NULL, NULL);
INSERT INTO tbl_item VALUES ('TB36', '3/8in x 6in Jaw/Eye Turnbu', 1, true,
'3/8in x 6in Galv Jaw/Eye Tnbkle', '3/8in x 6in Jaw/Eye Turnbuckle', 1.49, 0,
'40110', '50110', '50110', 'PREIPA', 'EA', 0, 0, 0, NULL, NULL);
INSERT INTO tbl_item VALUES ('RG6', 'RG6 8in Pipestock Offset', 1, false,
'Offset 6in (8in OAL) Powder Coated', 'RG6 Offset 8in OAL w/ 7/16in
alternating drill thrus @ 6in and 7in 5.5in x 5.5in Welded base Powder Coated
Zinc', 12.68, 1, '40110', '50110', '50110', 'DIR', 'EA', 4.3, 0, 0, NULL, NULL);
INSERT INTO tbl_item VALUES ('EXT10', '10ft Extension w/ Plates', 1, true,
'10ft Extension Brace w/ 5.5in Welded Plates Ships with EXTCAB', '10ft
Extension w/ two 5.5in Welded Pads - Powder Coat ZINC', 48, 0, '40110',
'50110', '50110', 'PREIPA', 'EACH', 0, 0, 0, '36.50', NULL);
INSERT INTO tbl_item VALUES ('EXTCAB', 'Extension Cable Kit', 1, true,
'Extension Cable Kit', 'Extension Cable - 11ft 1/4in Galv Cable with one end
pressed with 1/4in Galv Thimble and 3/8in x 2-1/2in Galv Eyebolt', 3.65, 0,
'40110', '50110', '50110', 'PREIPA', 'EACH', 0, 0, 0, '36.50', NULL);
INSERT INTO tbl_item VALUES ('WNY70STR', '1x27.5 Blk Web Strap w/ Buckle', 1,
true, '1in Black Web Strap w/ Adjustable Buckle Overall Length is 70cm
(27.56in)', 'Strap with Self Locking Buckle BLACK - 1in x 70cm', 1.03, 1,
'40100', '50100', '50100', 'PREIPA', 'ea', 0, 0, 0, NULL, NULL);
INSERT INTO tbl_item VALUES ('UBOLT38', '3/8x4x5 Sq. Bend U-Bolt', 1, true,
'3/8x4x5 U-Bolt,FW38(2),HN38(2)', 'SB-5, 38-16 4in x 5in Sq. Bend U-Bolt',
0.37, 1, '40110', '50110', '50110', 'PREIPA', 'EACH', 0, 0, 0, NULL, NULL);
INSERT INTO tbl_item VALUES ('II', 'Installation Instructions', 1, true,
'Installation Instructions for Incord Rack Guard Systems', NULL, 0, 1,
'40100', '50100', '50100', 'PREIPA', 'ea', 0, 0, 0, NULL, NULL);
#cat 12448.tbl_line_item.txt
12448,1,8,M3000OR,M-3000 HTPP ORANGE 4" Sq. Mesh\, Border: WP2B Size: 16' X
24' 9",161.00,1288.00
12448,2,32,TIE20,24" Nylon Tie 175#,0.32,10.24
12448,3,1,CAB2-1000,1/4" Galv Cable - 1000' Spool,200.00,200.00
12448,4,56,CC2,1/4" Galv Cable Clamps,0.35,19.60
12448,5,28,TM2,1/4" Galv Thimble,0.35,9.80
12448,6,27,EB36,3/8" X 6" Galv Nut/Eyebolt with (1) HN38\, (1) LW38,3.85,103.95
12448,7,700,IC,Galv ICE Fast Clip,0.18,126.00
12448,8,22,TB36,3/8"x6" Galv Jaw/Eye Tnbkle,6.00,132.00
12448,9,38,RG6,Offset 6" (8" OAL) Powder Coated,25.00,950.00
12448,10,9,EXT10,10' Extension Brace w/ 5.5" Welded Plates Ships with
EXTCAB,96.00,864.00
12448,11,9,EXTCAB,Extension Cable Kit,,
12448,12,85,WNY70STR,1" Black Web Strap w/ Adjustable Buckle Overall Length is
70cm (27.56"),2.25,191.25
12448,13,112,UBOLT38,3/8x4x5 U-Bolt\,FW38(2)\,HN38(2),,
12448,14,1,II,Installation Instructions for Incord Rack Guard Systems,,
______________________________________________
99main Internet Services http://www.99main.com