Help with update trigger

  • I am new to sql server and would appreciate any assistance in creating an update trigger.

    I have a view called

    dbo.view_badge which contains the following columns

    Year, stu_code, join_code, forename1, forename2, surname, dob, course, end_date

    I have a table called

    dbo.t_badge which contains the same columns

    Year, stu_code, join_code, forename1, forename2, surname, dob, course, end_date

    How do i create an update trigger which will update the table dbo.t_badge when any changes are made to the view

    dbo.view_badge.

    I would appreciate any assistance as i don't have any experience with triggers.

    Thanks,

    Iain

  • Hi, a very basic trigger you could use a starting point is:

    CREATE TRIGGER dbo.view_badge_upd ON dbo.view_badge

    INSTEAD OF UPDATE

    AS

    IF @@rowcount = 0

    RETURN

    IF ( SELECT COUNT(*)

    FROM Inserted

    ) > 1

    BEGIN

    PRINT 'Only one row at a time can be modified'

    RETURN

    END

    IF UPDATE(year)

    OR UPDATE(stu_code)

    BEGIN

    RAISERROR ( 'cannot change the year or the stu_code', 16, 1 )

    ROLLBACK TRAN

    RETURN

    END

    UPDATE dbo.t_badge

    SET forename1 = i.forename1

    , forename2 = i.forename2

    , surname = i.surname

    , dob = i.dob

    , course = i.course

    , end_date = i.end_date

    FROM inserted i

    WHERE i.year = t_badge.year

    AND i.stu_code = t_badge.stu_code

    RETURN

    Note that I assumed that year and stu_code are the primary keys (I did not really look into the table definition you had)

    The above will allow modifications to non-primary key columns, and only one row at a time.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • If you update a view, the table underlying that view is updated. You shouldn't need a trigger.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The table is created, then the data inserted from the view.

    I have a seperate procedure that fires every minute that works for inserts.

    However how do i do a triger for updates.

  • GilaMonster (1/15/2008)


    If you update a view, the table underlying that view is updated. You shouldn't need a trigger.

    Gail is of course right :). If you just want to have an extra level of indirection, and let people modify the underlying table of your view, you can do it via the view updating. The trigger is useful more for introducing extra constraints, like what columns and in what way you allow to modify.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Thanks,

    I'll give this a try.

  • icampbell (1/15/2008)


    The table is created, then the data inserted from the view.

    I have a seperate procedure that fires every minute that works for inserts.

    However how do i do a triger for updates.

    So the view is based on a different table than you want to update?

    Then look at Andras's post. He details an instead of trigger, which is what you'll need.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Andras,

    I modified your code slightly and it worked.

    Thanks,

    Iain.

Viewing 8 posts - 1 through 7 (of 7 total)

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