Post reply

create trigger for two case !?

  • 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

  • January 4, 2010 at 6:20 am

    #1098369

    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/

  • January 4, 2010 at 7:56 am

    #1098431

    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

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

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