Update Trigger Error

[Tom]

The trigger does set my CommAudit field to 1 when imaSlsCls is changed,
but at the end of the day when I issue the UPDATE command to set all CommAudit back to 0 I get an error. Is there a differant way to make it work?? See trigger, update command & error below.
Thanks for any help,
Tom

*** TRIGGER ***
CREATE TRIGGER [tblItemMaster_utr] ON dbo.tblItemMaster
FOR UPDATE
AS
IF (select imaSlsCls from inserted) <> (select imaSlsCls from deleted)
BEGIN
update tblItemMaster set commaudit = 1
from inserted, tblItemMaster
where inserted.imaitnbr = tblItemMaster.imaitnbr
END

*** UPDATE COMMAND ***
UPDATE tblItemMaster SET CommAudit = 0 WHERE CommAudit = 1

*** ERROR ***
Server: Msg 512, Level 16, State 1, Procedure tblItemMaster_utr, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

[Antony Melvin]

I think your problem is the line that says :

IF (select imaSlsCls from inserted) <> (select imaSlsCls from deleted)

because your end of day update also passes this test the trigger is fired so you set 1 to 0 and the trigger will then set 0 to 1, this could well create some recursion. The error message could also relate to this line because your update will create one record in the inserted and deleted tables, and then the trigger will do too. I assume that this line should say...

IF (select imaSlsCls from inserted) = 1 and
(select imaSlsCls from inserted) <> (select imaSlsCls from deleted)

so that your trigger is not fired at night.

------------
Tom at 3/23/01 10:12:57 AM

The trigger does set my CommAudit field to 1 when imaSlsCls is changed,
but at the end of the day when I issue the UPDATE command to set all CommAudit back to 0 I get an error. Is there a differant way to make it work?? See trigger, update command & error below.
Thanks for any help,
Tom

*** TRIGGER ***
CREATE TRIGGER [tblItemMaster_utr] ON dbo.tblItemMaster
FOR UPDATE
AS
IF (select imaSlsCls from inserted) <> (select imaSlsCls from deleted)
BEGIN
update tblItemMaster set commaudit = 1
from inserted, tblItemMaster
where inserted.imaitnbr = tblItemMaster.imaitnbr
END

*** UPDATE COMMAND ***
UPDATE tblItemMaster SET CommAudit = 0 WHERE CommAudit = 1

*** ERROR ***
Server: Msg 512, Level 16, State 1, Procedure tblItemMaster_utr, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

[Mauricio Ikegami]

The error is located at
IF (select imaSlsCls from inserted) <> (select imaSlsCls from deleted)

I believe only one record is updated (from your application), that&#39;s why the trigger works fine.
But if you try to update more than one record in a T-SQL statement (as in the one you run at the end of the day), the inserted and deleted table will have more than one record, so you cannot use <> to compare each select list.

You should use a cursor or
if Update(imaSlsCls)

Mauricio
------------
Tom at 3/23/01 10:12:57 AM

The trigger does set my CommAudit field to 1 when imaSlsCls is changed,
but at the end of the day when I issue the UPDATE command to set all CommAudit back to 0 I get an error. Is there a differant way to make it work?? See trigger, update command & error below.
Thanks for any help,
Tom

*** TRIGGER ***
CREATE TRIGGER [tblItemMaster_utr] ON dbo.tblItemMaster
FOR UPDATE
AS
IF (select imaSlsCls from inserted) <> (select imaSlsCls from deleted)
BEGIN
update tblItemMaster set commaudit = 1
from inserted, tblItemMaster
where inserted.imaitnbr = tblItemMaster.imaitnbr
END

*** UPDATE COMMAND ***
UPDATE tblItemMaster SET CommAudit = 0 WHERE CommAudit = 1

*** ERROR ***
Server: Msg 512, Level 16, State 1, Procedure tblItemMaster_utr, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.