[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trigger Syntax for Insert after Update

Posted on 2004-10-14
10
Medium Priority
?
1,050 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:Saqib Khan
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 1

Expert Comment

by:trouta01
ID: 12312507
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
ID: 12312536
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
ID: 12312545
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 13

Expert Comment

by:TommyTupa
ID: 12312555
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
ID: 12312598
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
 
LVL 23

Assisted Solution

by:Snarf0001
Snarf0001 earned 1000 total points
ID: 12312653
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:Saqib Khan
ID: 12312811
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
ID: 12312848
not an issue
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 1000 total points
ID: 12312857
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
ID: 12312901
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

867 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