Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-06-19
3
Medium Priority
?
311 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Integration Management Part 2
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

782 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