Click here to Skip to main content
15,921,203 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi

I have written a trigger which gets fired when you update or insert any record in a particular table.

Now, I want to execute one block of code in the trigger when i insert a record into the table.
and i want to execute another block of code in the trigger when i update any record in the table.

How can i put a condition in the trigger.


Regards,
Posted

Hi,

Try this sample code

SQL
CREATE TRIGGER dbo.TableName_IUD
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
 SET NOCOUNT ON;

 DECLARE @action CHAR(8)  
 IF COLUMNS_UPDATED() <> 0 -- delete or update?
 BEGIN     
  IF EXISTS (SELECT * FROM deleted) -- updated cols + old rows means action=update       
    SET @action = 'UPDATE'     
  ELSE
    SET @action = 'INSERT' -- updated columns and nothing deleted 
                           --means action=insert
 END 
 ELSE -- delete     
 BEGIN
  SET @action = 'DELETE'
 END

END


Thank you
 
Share this answer
 
You dont need to put conditional elements.
Create two triggers - one for insert and another for update.
Just choose the FOR (INSERT | UPDATE) based on your requirement[^].
 
Share this answer
 
v2
Comments
nm.nagaraju 12-Oct-12 2:50am    
Thanks, but we can have two triggers for insert and update.
But, i want them in one trigger instead of having two triggers...
:-)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900