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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

if statement in trigger code

I have triger code which is fired upon INSERT, DELETED, UPDATE
but I want run a different piece of code when deleted ie, I wnt to have "update set... from deleted" when there is DELETE.
how to check within the code whether there was DELETE or UPDATE or INSERT on table
0
Vasi04
Asked:
Vasi04
3 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It doesn't be better to make a trigger for each operation (DELETE, INSERT and UPDATE)?
0
 
muzzy2003Commented:
Check out Scott's reply to this PAQ:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21217494.html

The key code is:

IF EXISTS(SELECT TOP 1 'x' FROM inserted)
    IF EXISTS(SELECT TOP 1 'x' FROM deleted)
        SET @mod = 'U'
    ELSE
        SET @mod = 'I'
ELSE
    SET @mod = 'D'
0
 
boblahCommented:
Hi Vasi04,

how about:

IF EXISTS(SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
BEGIN
--rows have been deleted.
END

Cheers!
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
muzzy2003Commented:
Hi boblah - snap!
0
 
boblahCommented:
yep, you beat me to it :)
0
 
Vasi04Author Commented:
boblah soln is cool though
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.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now