CODEWITHSUNDEEP
sqltriggerssap-ase
dom
dom

Reputation: 651

sybase update trigger - check multiple rows for update

I am writing a trigger in a Sybase ASE database which fires on a update and compares the value before and after the update. I do this to verify if the update has actually changed the data. If this is the case it adds a row to a monitoring table. Unfortunately the trigger only works if the update command only affects 1 row. If it is affecting more than one row i get this error:

Msg 512, Level 16, State 5:
Server 'myserver', Procedure 'myproc', Line 2:
Subquery returned more than 1 value.  This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression.

my proc looks like this:

create trigger mytrigger on mytable for update as

set nocount on

/* now do the insert if colum was updated  */
if update(col_a) and (select col_a from inserted) not in (select col_a from deleted) 
begin
    insert into monitoring_table (login,tablename,field,action,pstamp) 
        select suser_name(),'mytable','col_a','U',getdate() from inserted
end
/* now do the insert if colum was updated  */
if update(col_b) and (select col_b from inserted) not in (select col_b from deleted) 
begin
    insert into monitoring_table (login,tablename,field,action,pstamp) 
        select suser_name(),'mytable','col_b','U',getdate() from inserted
end
go

any idea how i can get around this multi update problem within my trigger?

Upvotes: 0

Views: 5744

Answers (2)

Vikram
Vikram

Reputation: 8333

if the error is in trigger then the following should work: instead of:

if update(col_a) and (select col_a from inserted) not in (select col_a from deleted)

use

if update(col_a) and exists (select col_a from inserted where col_a not in (select col_a from deleted))

Upvotes: 2

aF.
aF.

Reputation: 66697

This should work:

create trigger mytrigger on mytable for update as

set nocount on

/* now do the insert if colum was updated  */
if update(col_a) and (select col_a from inserted where col_a not in (select col_a from deleted) 
begin
    insert into monitoring_table (login,tablename,field,action,pstamp) 
        select suser_name(),'mytable','col_a','U',getdate() from inserted
end
/* now do the insert if colum was updated  */
if update(col_b) and (select col_b from inserted where col_b not in (select col_b from deleted) 
begin
    insert into monitoring_table (login,tablename,field,action,pstamp) 
        select suser_name(),'mytable','col_b','U',getdate() from inserted
end
go

Upvotes: 1

Related Questions