incorrect data update on Sybase trigger execution

Question

I have a table test_123 with the column as:

int_1         (int), 
datetime_1    (datetime), 
tinyint_1     (tinyint), 
datetime_2    (datetime)

So when column datetime_1 is updated and the value at column tinyint_1 = 1 that time i have to update my column datetime_2 with column value of datetime_1

I have created the below trigger for this.. but with my trigger it is updating all datetime2 column values with datetime_1 column when tinyint_1 = 1 .. but i just want to update that particular row where datetime_1 value has updated( i mean changed)..

Below is the trigger..

CREATE TRIGGER test_trigger_upd
ON test_123 

FOR UPDATE
AS
FOR EACH STATEMENT 
IF UPDATE(datetime_1) 

BEGIN


UPDATE test_123 
SET test_123.datetime_2  = inserted.datetime_1                     
WHERE test_123.tinyint_1 = 1

END

Seems like your where clause might be the issue. I would add a key column so you can ensure you are only updating one row at a time. — Mike Gardner, Jan 21 '15 at 16:03
i cannot add and extra column here .. i mean as trigger cannot have a input parameter. Is there nothing in sybase where we can do FOR EACH ROW statement ..because when i am trying to do this it is giving me compilation error — Arpan, Jan 21 '15 at 21:12
Sorry, I meant add the column to the table when it is created - prior to your trigger creations/execution. Rob V is one of the most knowledgeable people in the world when it comes to Sybase db products, so his answer is probably the way to go. — Mike Gardner, Jan 22 '15 at 02:53

score 1 · Answer 1 · edited Jun 06 '15 at 00:57

ROW-level triggers are not supported in ASE. There are only after-statement triggers.

As commented earlier, the problem you're facing is that you need to be able to link the rows in the 'inserted' pseudo-table to the base table itself. You can only do that if there is a key -- meaning: a column that uniquely identifies a row, or a combination of columns that does so. Without that, you simply cannot identify the row that needs to be updated, since there may be multiple rows with identical column values if uniqueness is not guaranteed. (and on a side note: not having a key in a table is bad design practice -- and this problem is one of the many reasons why).

A simple solution is to add an identity column to the table, e.g.

ALTER TABLE test_123 ADD idcol INT IDENTITY NOT NULL

You can then add a predicate 'test_123.idcol = inserted.idcol' to the trigger join.

incorrect data update on Sybase trigger execution

1 Answers1