Link to home
Start Free TrialLog in
Avatar of Saqib Khan
Saqib KhanFlag for United States of America

asked on

Trigger Syntax for Insert after Update

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
Avatar of trouta01
trouta01

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
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
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'
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
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


   
SOLUTION
Avatar of Snarf0001
Snarf0001
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Saqib Khan

ASKER

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
not an issue
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.