Solved

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

18 Experts available now in Live!

Get 1:1 Help Now