CODEWITHSUNDEEP
sqlstored-procedurestriggerssyntax-errorinformix
CheeseConQueso
CheeseConQueso

Reputation: 6051

Informix SQL - What is wrong with this simple stored procedure &| trigger syntax?

IBM Informix Dynamic Server Version 11.50.FC6

I am trying to execute a simple stored procedure from within an update trigger. Together, they are used to update a field with the current timestamp when another field in the same row is updated.

Table sp_test:

id          (serial int, unique, not null, primary key)    
stat        (char(1), not null, default="A")
add_date    (date, not null, default today)
upd_date    (date, null)

The stored procedure code is:

create procedure upd_row_date_proc (cid int)
update sproc_trig_rec set upd_date = current where id = cid;
end procedure;

This executes fine and creates the routine, but the trigger I am trying to implement on updates is not working.

The trigger code is:

create trigger upd_row_date_trig
update of stat on sproc_trig_rec
after (execute procedure upd_row_date_proc(id));

I've tried a bunch of syntax variations, but cannot get it to work.

I usually get my error on the ( char of the 3rd line. Here's the error code:

  201: A syntax error has occurred.
Error in line 3
Near character position 0

Does anyone know what I'm doing wrong in the syntax of the trigger? Could this type of updating be defined in the creation of the table, or do I need to accomplish it by doing it the way described above?

Thanks for any help

Upvotes: 1

Views: 2127

Answers (1)

CheeseConQueso
CheeseConQueso

Reputation: 6051

This finally worked for me

create trigger ken_trig
update of stat on sproc_trig_rec
referencing old as ken_pre_upd
for each row (execute procedure ken_proc(ken_pre_upd.id));

Upvotes: 2

Related Questions