Error while executing an update query having triggers built for the same

Database
Enthusiast

Error while executing an update query having triggers built for the same

I am having some triggers built on a table. PFB the trigger and table structure.

Create Multiset table test_row

(

Name varchar(20),

Age int

) Primary index(Name);

Insert into test_row values ( 'abhi',24);

Insert into test_row values ( 'raj',22);

Create Multiset table name_row

(

Name varchar(20),

Prev_age int,

cur_age int,

date_changed date

) Primary index(Name);

Create Multiset table name_row1

(

Name varchar(20),

Age int,

Date_changed date

) Primary index(Name);

Create trigger test_row_upd1

after update of age on test_row order 100

referencing new as after_change

referencing old as before_change

for each row

insert into name_row values(before_change.name,before_change.age,after_change.age,date);

Create trigger test_row_upd2

after update of age on test_row order 200

for each statement

insert into name_row1 (Date_changed, name, age) values(date,name,age);

Now, i am trying to execute the below mentioned update query.

Update test_row

set age = 20

where name in ('raj','abhi');

I am getting an error message 'Teradata reference(s) detected where constants expected.

Please throw some light on this.

4 REPLIES
Enthusiast

Re: Error while executing an update query having triggers built for the same

if someone has come across the aforesaid situation, please let me know the solution.

Enthusiast

Re: Error while executing an update query having triggers built for the same

anyone?

Senior Apprentice

Re: Error while executing an update query having triggers built for the same

The code you posted will result in a syntax error, as there's only one REFERENCING, but test_row_upd1 has two.

The second trigger will fail to execute because there's no REFERENCING to specify a name for OLD_NEW_TABLE, so you can't access it's data in the INSERT.

Dieter

Enthusiast

Re: Error while executing an update query having triggers built for the same

hey! thanks dnoeth