Saqib Khan
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
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
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
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'
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),getdat e(),101) as DateReopened
from dbo.assignment
as a inner join
dbo.calllog
as cl on (a.callid = cl.custID)
end
END
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),getdat
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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