Solved

SQL trigger- only insert select details

Posted on 2008-06-20
2
683 Views
Last Modified: 2008-06-28
I have a trigger shown in the snippet,

It inserts the records in opdetm joined by opheadm.order_no = opdetm.order_no, into opdetm_audit when opheadm's status is updated to 9,

what i want to add now is that it will only add the opdetm records that do not exist in opdetm_audit2 (this is not a typo, there is an audit and an audit2 table)
joined by
opdetm.order_no = opdetm_audit2.order_no and
opdetm.order_line_no = opdetm_audit2.order_line_no,

How would this be done?
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)

 

declare @Status char(1)

declare @OrderNo char (10)

declare @OrderLineNo char(1)

declare @DateEntered datetime

declare @Exist float
 

select @Status = [status]from inserted with (NOLOCK)

select @OrderNo = order_no from inserted with (NOLOCK)

select @DateEntered = date_entered from inserted with (NOLOCK)

 

if @Status = 9 

	Begin

		Insert into opdetm_audit (order_no,order_line_no,line_type,order_line_status,order_qty,despatched_qty,allocated_qty,val) 

Select 

		order_no,

		order_line_no,

		line_type,

		order_line_status,

		order_qty,

		despatched_qty,

		allocated_qty,

		val
 

from [scheme].[opdetm]with (NOLOCK) where order_no = @OrderNo

and @DateEntered < CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)
 

	End

 

END

Open in new window

0
Comment
Question by:meteorelec
2 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 21829751
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)
 
declare @Status char(1)
declare @OrderNo char (10)
declare @OrderLineNo char(1)
declare @DateEntered datetime
declare @Exist float
 
select @Status = [status]from inserted with (NOLOCK)
select @OrderNo = order_no from inserted with (NOLOCK)
select @DateEntered = date_entered from inserted with (NOLOCK)
 
if @Status = 9
      Begin
            Insert into opdetm_audit (order_no,order_line_no,line_type,order_line_status,order_qty,despatched_qty,allocated_qty,val)
Select
            A.order_no,
            A.order_line_no,
            A.line_type,
            A.order_line_status,
            A.order_qty,
            A.despatched_qty,
            A.allocated_qty,
            A.val
 
from [scheme].[opdetm] A ,opdetm_audit2 B  with (NOLOCK) where order_no = @OrderNo
and @DateEntered < CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)
and A.order_no = B.order_no and
A.order_line_no = B.order_line_no
 
      End
 
END
0
 
LVL 2

Accepted Solution

by:
meteorelec earned 0 total points
ID: 21831327
I figured it out myself, i just do a left join on the opdetm_audit2 where order no is null
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard 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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

910 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

22 Experts available now in Live!

Get 1:1 Help Now