August 24, 2015 at 9:20 am
hello,
I have two tables i.e sales_header and sales_item_details
And there is another table sales_audit. (audit_id, audit_text)
What i want to do is to put an on delete trigger on sales_header and insert all relavent information from sales_header and sales_item_details table to sales_audit table in audit_text field. i would loop through sales_item_details records and make a single string to transfer to audit table.
Delete sequence is first delete from sales_item_details then delete from sales_header but trigger is set on sales_header table.
Is it possible ?
August 24, 2015 at 9:41 am
Does this mean that you don't have foreign key constraints in place?
August 24, 2015 at 9:48 am
there is foreign key constraint
August 24, 2015 at 10:35 am
If there's a foreign key, the trigger must be on the details table as its contents will be deleted before the header. You also don't need to loop to create a single string, there's a method available to do it in a single statement and it's explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Here's an example.
ALTER TRIGGER TD_SalesD
ON [dbo].[Sales_Detail]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON
INSERT Sales_Audit(audit_text)
SELECT 'SalesOrderID: ' + CAST( h.SalesOrderID AS varchar(5)) +
' Products: ' +
STUFF((SELECT ', ' + CAST( ProductID AS varchar(5))
FROM deleted d
WHERE d.SalesOrderID = h.SalesOrderID
ORDER BY d.SalesOrderDetailID
FOR XML PATH('')), 1, 2, '')
FROM deleted h
GROUP BY h.SalesOrderID
END
I must say that this seems a bad idea, as you won't be able to query your audit table directly and would make any real audit a serious headache.
August 25, 2015 at 1:48 am
Thanks for your help :-):-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply