Solved

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

Posted on 2008-06-19
3
262 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now