Update Trigger Question

[Rob7]

Hi All,

I have looked at several examples of triggers that fire on update of a column, but I would like to fire a trigger on the the update of a column of a particular record. Ideally my trigger would look something like this:

CREATE TRIGGER test_trigger
ON [dbo].[IMINVLOC_SQL]
FOR UPDATE
AS
IF UPDATE (qty_on_hand)
WHERE item_no = '15001' and loc = '01'

When I try this, the syntax checker complains about the WHERE keyword. Anybody tried this before?

Thanks,

Rob


Cytec Corporation
rbrown@cyteccorp.com

 

[vongrunt]

UPDATE() checks whether column qty_on_hand is updated or not.

Wanna check updated values, refer to pseudotables inserted and deleted, available within trigger's body.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLSister]

After the IF Update statment, then you write the SQL statment of what you want to do, usually an update statment of some kind

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SQLDenis]

Check this out about update triggers

http://sqlservercode.blogspot.com/2005/12/fun-with-sql-server-update-triggers.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Rob7]

So it looks like I am not going to be able to do what I thought I could do. What I want to do is monitor a particular field in a particular record. The field is called 'qty_on _hand'. This would be constrained by the field 'item_no' and field 'loc'. A change to this field only is what I want to fire the trigger. Then when the trigger fires, I want to evaluate two other qty_on_hand values in two separate tables and have the three qty_on_hand vales output to some log file, along with the trigger time. Can I call a stored procedure from a trigger?

Cytec Corporation
rbrown@cyteccorp.com

 

[SQLDenis]

You can call a stored proc and this is probably better since you don't want too much code in a trigger anyway

CREATE TRIGGER trTest
ON Table
FOR UPDATE
AS

IF @@ROWCOUNT =0
RETURN

IF UPDATE(value)
BEGIN
if exists (SELECT * FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1))
BEGIN
EXEC procedure ....
END

END

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/