Identifying the row being updated with Sybase triggers.....

[Onural, Gul [SKY:1V70:EXCH]]

Using ASA 7.02...

I want to use an update trigger to mark a record when it has been modified.
I have added an updated_flag to the table (say Employee)
and I want to set this flag to 1 when a record is updated.

My question is :

- How do I know which row caused the update trigger to be fired and how do I
identify this row?

In this particular case, the only thing I want to know is whether the row is
updated or not, so that I can set
the updated flag to 1 using an update sql statement.

My trigger's (pseudo) code looks like this :

create trigger dba.emp_update after update of empId,empSalary, empName on
dba.Employee
referencing old as o new as n
for each row
begin
message 'Something changed......' type info to client;
update employee set updated_flag = 1
end

Since I don't know how to specify the row which caused my trigger to be
triggered I end up having my updated_flag
set for all the records in the table. old and new tables doesn't help me to
identify the row which is being updated.

Gul

[Onural, Gul [SKY:1V70:EXCH]]

That was it Breck. Thank you very much.

Gul

"Breck Carter" <NOSPAM_...@risingroad.com> wrote in message
news:ge220ugrsed1f472m...@4ax.com...
> You are actually all set... your trigger "sees" one single row, the
> one being updated.
>
> In a FOR EACH ROW trigger the row being updated is the one in the
> REFERENCING OLD and NEW clauses. If you change your trigger to BEFORE
> UPDATE then you can modify the REFERENCING NEW column you're
> interested in via SET instead of UPDATE.
>
> This is described in this paragraph in the 7.0.3 Help...
>
> ASA Reference
> CHAPTER 9. SQL Statements
> CREATE TRIGGER statement
> Parameters
> REFERENCING clause
>
> ...pay close attention to the differences between row-level and
> statement-level triggers in the description, it's like a whole
> different world :)
>
> Aside: If the "calling" program does a ROLLBACK then the actions of
> the trigger are rolled back too, so there's no penalty to using a
> BEFORE trigger instead of AFTER.
>
> Here's a complete code sample...
>
> CREATE TABLE trigtest
> ( pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT,
> data1 INTEGER NOT NULL DEFAULT 0,
> updated_flag INTEGER NOT NULL DEFAULT 0,
> PRIMARY KEY ( pkey ) );
>
> CREATE TRIGGER tru_trigtest
> BEFORE UPDATE ON trigtest
> REFERENCING
> OLD AS old_trigtest
> NEW AS new_trigtest
> FOR EACH ROW
> BEGIN
> SET new_trigtest.updated_flag = 1;
> END;
>
> INSERT INTO trigtest VALUES ( DEFAULT, DEFAULT, DEFAULT );
> INSERT INTO trigtest VALUES ( DEFAULT, DEFAULT, DEFAULT );
> INSERT INTO trigtest VALUES ( DEFAULT, DEFAULT, DEFAULT );
> COMMIT;
>
> UPDATE trigtest SET trigtest.data1 = 999 WHERE trigtest.pkey = 2;
> COMMIT;
>
> SELECT * FROM trigtest ORDER BY trigtest.pkey;
>
> Breck

> bca...@risingroad.com
> Palm UltraLite Enterprise Applications
> -----
> Visit http://www.risingroad.com for Tips and Techniques
> [TeamSybase]

[Breck Carter]

Breck

bca...@risingroad.com

[Richard Biffl]

Your trigger's UPDATE statement should have a WHERE clause, specifically
something like WHERE employee.EmpID = n.EmpID.

Richard

"Onural, Gul [SKY:1V70:EXCH]" <onu...@americasm01.nt.com> wrote in message
news:3eJAxCV...@forums.sybase.com...