asked on

Insert trigger to insert multiple records from another table (When header is updated, insert details)

I have a table [scheme].[opheadm] which is the order header table, (one order, one record)
and i have another table [scheme].[opdetm] which is the order detail table (one order, mulitiple records).
These two tables join by the field [order_no]

What i want to do is create a trigger on the header table ([scheme].[opheadm]) that when the field [scheme].[opheadm].[status] is updated to equal '9' that all the records in the detail table ([scheme].[opdetm]) will be inserted into another table ([scheme].[opdetm_audit])

How should i go about this?

meteorelec

ASKER

I have attached a snippet of my attempt, but it just doesn't seem to work

ALTER TRIGGER [scheme].[metauddel]
   ON  [scheme].[opheadm] 
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
IF UPDATE (status)
BEGIN
insert into [scheme].[opdetm_audit]
select
		de.order_no,
		de.order_line_no,
		de.line_type,
		de.order_line_status,
		de.order_qty,
		de.despatched_qty,
		de.allocated_qty,
		de.val
 
from inserted i
join scheme.opdetm de (NOLOCK) on i.order_no = de.order_no
and i.status = 9
 
END
 
END

Open in new window

ASKER CERTIFIED SOLUTION

Chrisedebo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

meteorelec

ASKER

Thanks, Thats done my job,

Maybe you could keep an eye out for my next question. it;ll be posted in ten minutes,