June 19, 2009 at 2:56 pm
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.
June 29, 2009 at 8:50 am
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
June 29, 2009 at 9:02 am
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
June 29, 2009 at 3:56 pm
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