?
Solved

Update Trigger

Posted on 2005-04-18
7
Medium Priority
?
230 Views
Last Modified: 2010-03-19
I need an update trigger that fires after update which will look at my QuantityOrdered and QuantityDelivered fields and set a UserID field to SUser_SName() if they're equal.

Anyone?
0
Comment
Question by:natejacobs
  • 4
  • 2
7 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 13806821
Thsi kind of code should do the trick

create trigger utg_yourtable_upd on yourtable
for update
as
if update(QuantityDelivered) or update(QuantityOrdered )
begin
update a
set UserID = SUser_SName()
from yourtable a
inner join inserted i on a.primarykey = i.primarykey
where i.QuantityOrdered  = i.QuantityDelivered
end
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13806867
Of course you'll need to replace "yourtable" and "primarykey"
with your actual table name and it's primary key column name(s) (might be a composite key)
0
 
LVL 5

Expert Comment

by:obahat
ID: 13807109
CREATE TRIGGER [dbo].[MyTrigger] ON [dbo].[MyTable] AFTER UPDATE
AS
BEGIN
    UPDATE [dbo].[MyTAble]
    SET [UserID] = SUser_SName()
    FROM [dbo].[MyTAble] a
          INNER JOIN [Inserted] i
            ON a.[PKColumns] = i.[PKColumns]
   WHERE i.[QuantityOrdered] = i.[QuantityDelivered]
            ON a.[DeviceNumber] = i.[DeviceNumber]
END
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 3

Author Comment

by:natejacobs
ID: 13807701
What I really need to do is increase the QuantityDelivered by 1 on an update SP

UPDATE  
     MyTable
SET          
     QuantityDelivered = QuantityDelivered +1
WHERE    
     MyTablePK = @ID

Then I need to check the 2 columns (QuantityOrdered, QuantityDelivered) to see if they're equal.
If they are equal, I need to set the EmployeeID.

Ideas?
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13807762
Then this should do the trick

UPDATE  
     MyTable
SET          
     UserID = case
            when QuantityDelivered + 1 = QuantityOrdered then SUser_SName()
           else UserID
     end,
     QuantityDelivered = QuantityDelivered +1,
WHERE    
     MyTablePK = @ID

0
 
LVL 3

Author Comment

by:natejacobs
ID: 13807872
Shouldn't QuantityDelivered be incremented first? - and then the UserID updated if QuantityDelivered  =  QuantityOrdered ?  

I only want to set the userID field to SUser_SName() IF the two fields are equal after QuantityDelivered has been updated.

Of course I could be missing your point, too  :)
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 1000 total points
ID: 13807915
>>Shouldn't QuantityDelivered be incremented first? - and then the UserID updated if QuantityDelivered  =  QuantityOrdered ?  <<

No, the "new" updated value is not visible in the "current update"

You'd have to split the logic into 2 separate update statements, thus making the code slower.
It would also imply transaction handling

Hilaire

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

850 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