Solved

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

Posted on 2008-06-19
3
275 Views
Last Modified: 2010-03-20
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?
0
Comment
Question by:meteorelec
  • 2
3 Comments
 
LVL 2

Author Comment

by:meteorelec
ID: 21820283
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

0
 
LVL 7

Accepted Solution

by:
Chrisedebo earned 500 total points
ID: 21820311
This should do what you want.

CREATE TRIGGER scheme.OrderDetailAudit 
   ON  scheme.opheadm 
   AFTER UPDATE
AS 
 
declare @Status int
declare @OrderNo int
 
select @Status = Status, @OrderNo = Order_No from inserted
 
if @Status = 9 
	Begin
		Insert into opdetm_audit (comma,separated,field,list) Select same,field,list,here from opdetm where order_no = @orderno
	End
 
GO

Open in new window

0
 
LVL 2

Author Comment

by:meteorelec
ID: 21820436
Thanks, Thats done my job,

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

0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question