Solved

Trigger Syntax for Insert after Update

Posted on 2004-10-14
10
1,001 Views
Last Modified: 2008-01-09
Hi,
I have a Table Caled ORDERS. in that table there is a Column named STATUS, so whenever the Status is updated to "I" I want to insert information about that order into a new Table Called YesterdayLog.

by default Status has Value "N" and it keep on changing. only if its "I" i want to Fire the Trigger to insert records into the Table. Now is it possible to do a Join within the Trigger to insert some other Values as well?

for Example Orders Table has Order numbers, Lines table has the Items for those orders, can I insert items as well into new Triger when it Fires?

Please Provide the Syntax.

Thanks
0
Comment
Question by:adilkhan
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 1

Expert Comment

by:trouta01
Comment Utility
The key is to use the inserted table in the trigger, it contains the data for the inserted and updated rows only

create trigger ....

insert into YesterdayLog
(Field1, Field2, Field3, .... Fieldn)
Select FieldA, FieldB, FieldC,  .... Fieldn)
From inserted
Where inserted.Status = 'I'

You will need two triggers, one for the update and one for the insert
0
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
the type of trigger you want is straightforward.
Obvioulsy, you need to modify to suit your actual table structures etc.
inserted is a special system table which contains a copy of the rows which have just been inserted or updated, and so it is this that you want to use as your source of data (and you can join this to the LineTable etc)

create trigger MyTrig on ORDERS
for INSERT, UPDATE
as
begin

     INSERT INTO YseterdayLog
     SELECT OrderID, OrderDetails, LineDetails
     FROM inserted i
     JOIN LinesTable l on i.OrderID = l.OrderID
     WHERE i.STATUS = 'I'

end
0
 
LVL 1

Expert Comment

by:trouta01
Comment Utility
Actually the update trigger will need some more work, the trigger above will work for inserted.  If you only want the row to be inserted when the value changes to an 'I', you will need to use the deleted table to compare for a change

create trigger  .... for update

insert into YesterdayLog
(Field1, Field2, Field3, .... Fieldn)
Select FieldA, FieldB, FieldC,  .... Fieldn)
From inserted inner join delete on inserted.<<key>> = deleted.<<key>>
Where inserted.Status = 'I' and deleted.Status <> 'I'
0
 
LVL 13

Expert Comment

by:TommyTupa
Comment Utility
Here's a trigger.  Maybe this will help:



Create  TRIGGER Update_ReopenedCalls

ON Calllog
FOR update
AS
BEGIN
      if exists
 (SELECT callstatus FROM
 calllog WHERE callstatus = 'Reopened')
begin
insert into ReopenedCalls
select
cl.callid,  cl.recvddate, a.assignee, cl.calltime, cl.cause,cl.calltype, cl.calldesc,
convert(varchar(10),getdate(),101) as DateReopened
from dbo.assignment
as a inner join
dbo.calllog
as cl on (a.callid = cl.custID)

end
   
END
0
 
LVL 6

Expert Comment

by:JaffaKREE
Comment Utility
CREATE TRIGGER
on orders
for INSERT as
BEGIN
   DECLARE @mystatus char
   Select @mystatus = Select STATUS from INSERTED
   if (@mystatus = 'I') then
   BEGIN
     Insert into YesterdayLOG (Select * from INSERTED)
         .... do your thing ...  whatever else ...
   END
END


   
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 22

Assisted Solution

by:Snarf0001
Snarf0001 earned 250 total points
Comment Utility
Slight modification to BillAn1's code will take care of trouta01's concern (which is valid).  Just a little cleaner and faster.

create trigger MyTrig on ORDERS
for INSERT, UPDATE
as
begin

  if Update(status)
     INSERT INTO YseterdayLog
     SELECT OrderID, OrderDetails, LineDetails
     FROM inserted i
     JOIN LinesTable l on i.OrderID = l.OrderID
     WHERE i.STATUS = 'I'

end
0
 
LVL 23

Author Comment

by:adilkhan
Comment Utility
Thank You.

I got it to work with your Help. Now my Question is Around 30 users work on Status every hour. This means Status keeps on changing like 30 times in one hour. Will it Cause any performance issue?
is it ok to use?

I am using Following code

Create Trigger STAT
on Forder_header
For Update
As
      if Update(ORDR_STATUS)
            Insert into STAT_TABLE
            Select ORDR_NUMBER, F.STK_CATEGORY,  L.ORLN_STK_CODE, getDate()
            From Inserted i, FOrder_Lines L, Fstock F
            Where i.ORDR_NUMBER = L.ORLn_ORNUM
            AND L.ORLN_STK_CODE = F.STK_CODE
            AND i.ORDR_STATUS = 'I'
      

Go

Please Advise.

Thanks
0
 
LVL 6

Expert Comment

by:JaffaKREE
Comment Utility
not an issue
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 250 total points
Comment Utility
you are talking probably < 1 msec to run the trigger, so no, 30 changes per hour will not be a problem.
if you had 30 per second you might get concerned about performance.....
0
 
LVL 1

Expert Comment

by:trouta01
Comment Utility
Maybe I am wrong, but I thought the update() function in the trigger returned true if the value had been updated, not changed.  So if the current value was 'I' and the application updated the value to 'I', another row would be added to the YesterdayLog table.  It would depend if the appliaction updates the whole row or only the changed fields.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

9 Experts available now in Live!

Get 1:1 Help Now