create trigger for two case !?

Question

create trigger for two case !?

bernardchristophe

Old Hand

Points: 366
More actions
January 4, 2010 at 3:21 am

#141399

Hi,
happy new year for everybody ...enjoy for 2010 🙂
I'm a beginner with t sql and i work with sql server 2005.
I must to create a trigger for to insert some value in other table..
- if it an insert => add a new record in other table x
- if it an update =>add record in other table x
here is it the table x where data will be store (after trigger)
------------------------------------------
evenement => id primarey key
cours => id
typeEvenement => bit (insert or update)
dateEvent => date of insert
my question is :
is it possible to create just one trigger or it PREFERABLE to create two trigger (one for insert / one for update)
because i wonder if it possible to DISTINGUISHED if the trigger is fired FOR INSERT OR UPDATE !?
Thanks for all and for your sample 😉
Christophe

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Personally, I’d do it in 2 different triggers because I’m doing completely different actions in both scenarios. Having said that, you can create 1 trigger and check if the amount of records in both views INSERTED and DELETED. If both of the views have any records, then the trigger was fired because of an update operation. If only INSERTED view has records, then the trigger was fired because of an insert statement.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You can do it either way. If you are doing 2 totally different things on the different actions then 2 triggers would probably be best, but if you are doing inserts into the same table for both Insert & Update with the only difference being the value in the bit column then I'd do one trigger. A one trigger solution would look something like this:

CREATE TRIGGER test ON dbo.testv

FOR INSERT, UPDATE

AS

SET NOCOUNT ON;

INSERT INTO x

(

evenement,

cours,

typeEvenement,

dateEvent

)

SELECT

I.evenement,

I.cours,

CASE

WHEN D.evenement IS NULL THEN 0 /* Insert */

ELSE 1 /* Insert */

END,

GETDATE()

FROM

inserted I LEFT JOIN

deleted D ON

I.evenement = D.evenement;

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question