Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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