[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Trigger Syntax for Insert after Update

Posted on 2004-10-14
10
Medium Priority
?
1,049 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

656 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