Results 1 to 9 of 9

Thread: Trigger

  1. #1
    Join Date
    Oct 2007
    Posts
    30

    Unanswered: Trigger

    I have a trigger that i created that works great

    CREATE TRIGGER tr_LogUserAllocation
    ON dbo.projects
    FOR INSERT, UPDATE
    AS

    INSERT INTO
    dbo.ProjectAllocationLog (projectid, olduserid, newuserid, createdate)
    SELECT
    i.Projectid,
    d.userid,
    i.userid,
    GETDATE()
    FROM
    inserted i LEFT JOIN deleted d
    ON
    i.Projectid = d.Projectid
    WHERE
    i.userid <> d.userid
    OR (i.userid is not null and d.userid is null)


    can i get the trigger to do more that one thing, ie If i want the above action to work only when I update the userid field, can I put IF NOT UPDATE(userid)

    and if i can, can I then use the IF NOT UPDATE(another field) to do a completly different action if

    CREATE TRIGGER tr_LogUserAllocation
    ON dbo.projects
    FOR INSERT, UPDATE
    AS

    if not update(userid)

    INSERT INTO
    dbo.ProjectAllocationLog (projectid, olduserid, newuserid, createdate)
    SELECT
    i.Projectid,
    d.userid,
    i.userid,
    GETDATE()
    FROM
    inserted i LEFT JOIN deleted d
    ON
    i.Projectid = d.Projectid
    WHERE
    i.userid <> d.userid
    OR (i.userid is not null and d.userid is null)


    if not update (another Field)


    therefore i would be able to do loads of different things, depending on what field has changed?

  2. #2
    Join Date
    Oct 2007
    Posts
    5
    hi,

    i think, this can help

    CREATE TRIGGER tr_LogUserAllocation
    ON dbo.projects
    FOR INSERT, UPDATE
    AS

    if update(projectid) or update(olduserid) or ... etc.

    begin
    select 1
    end

    else

    INSERT INTO
    dbo.ProjectAllocationLog (projectid, olduserid, newuserid, createdate)
    SELECT
    i.Projectid,
    d.userid,
    i.userid,
    GETDATE()
    FROM
    inserted i LEFT JOIN deleted d
    ON
    i.Projectid = d.Projectid
    WHERE
    i.userid <> d.userid
    OR (i.userid is not null and d.userid is null)

  3. #3
    Join Date
    Oct 2007
    Posts
    30
    what does this do?

    begin
    select 1
    end

  4. #4
    Join Date
    Oct 2007
    Posts
    5
    it does nothing, and that's it. It is there only for case that any of if condition would be true

  5. #5
    Join Date
    Oct 2007
    Posts
    30
    So basically my code inside the trigger should read something like

    if update(projectid)
    begin
    select 1
    end

    else

  6. #6
    Join Date
    Oct 2007
    Posts
    30
    whooops that was a mistake what i meant to write was

    so basically my code inside the trigger should read something like

    if update(projectid)
    begin
    some code .......
    end
    else
    if update(some oher field)
    begin
    some code .......
    end

    etc etc

  7. #7
    Join Date
    Oct 2007
    Posts
    5

    Thumbs up

    exactly

  8. #8
    Join Date
    Oct 2007
    Posts
    30
    brilliant, thank you very much x

  9. #9
    Join Date
    Oct 2007
    Posts
    5
    that's all right

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •