Execute stored procedure in AFTER UPDATE trigger

  • Hi there, I have a stored procedure I want to execute when some field in a table gets updated. So I think the best place to do this is in the AFTER UPDATE trigger for the table.

    When the field gets updated in the table, I want to pass the new field value as a parameter to the stored procedure, which expects one parameter only:

    create procedure ACTUPRECUARTO(@cuartoid integer)

    How can I do this with T-SQL? Thanks.

  • Something like this?

    CREATE TRIGGER TriggerName

    AFTER UPDATE

    AS

    BEGIN

    IF UPDATE (ColumnName)

    BEGIN

    DECLARE @cuartoid int

    SELECT @cuartoid = ColumnName from INSERTED

    EXEC ACTUPRECUARTO @cuartoid

    END

    END

    Eli

  • the above trigger example assumes thre would only be ONE row ever updated at any one time...it best practice is to assume more than one row comes from the INSERTED table.

    I would actually do the real work your procedure does in the trigger, rather than calla procedure, so you can do a set based operation, but here's a cursor example that would do each row:

    CREATE TRIGGER TriggerName FOR YOURTABLE

    AFTER UPDATE

    AS

    BEGIN

    IF UPDATE (ColumnName)

    BEGIN

    DECLARE @cuartoid int

    declare c1 cursor for

    SELECT select ColumnName FROM INSERTED

    open c1

    fetch next from c1 into @cuartoid

    While @@fetch_status -1

    begin

    EXEC ACTUPRECUARTO @cuartoid

    fetch next from c1 into @cuartoid

    end

    close c1

    deallocate c1

    END

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • This is gonna be a one-by-one update operation (primary key based), so it seems I don't need a cursor for this one. But indeed great cursor example for a newbie like me, I'll keep it handy for future multi-row updates. Many thanks to both.

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

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