trigger and identity

Database

trigger and identity

I create a table A(id,name), id is IDENTITY column(also is unnique primary key)

then I create a trigger on table B, and a row is inserted into table A when a row is updated in table B.
just like:
create trigger test_tri AFTER UPDATE on B REFERENCING NEW as newrow for each ROW(insert into A(name) values
(newrow.name));

When I update more than one rows in table B, Teradata database reported "2801: duplicate unique prime key erorr in A)

IDENTITY can't work in ROW trigger?? I don't want to use "REFERENCING NEW_TABLE".

Do anyone know this?
2 REPLIES
rgs
Enthusiast

Re: trigger and identity

Apparently it is a known problem. The workaround is to change it to a statement trigger.
Enthusiast

Re: trigger and identity

Hi All,

I am facing problem while updating audit table using the after delete and after insert triggers.
I have a table that contains an identity column which when used in insert trigger to populate the audit table gives the following error:

*** Failure 5859 A triggered action statement or the WHEN clause cannot reference an Identity Column.

Surprisingly my delete trigger do not give me such error.

here is the code:

Table X1:
---------

CREATE SET TABLE x1 ,
NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
(
emp_id DECIMAL(3,0) NOT NULL GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999
NO CYCLE),
emp_name VARCHAR(30) ,
emp_dept VARCHAR(10)
)
UNIQUE PRIMARY INDEX ( emp_id );

Table X1_audit:
--------------

CREATE SET TABLE x1_audit ,
NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
(
emp_id DECIMAL(3,0) ,
change_type char(3),
change_date date,
emp_name VARCHAR(30)
);

Delete Trigger:
--------------

CREATE TRIGGER x1_d_t
AFTER DELETE
ON x1
REFERENCING OLD AS old_row
FOR EACH ROW
( INSERT INTO x1_audit VALUES(old_row.emp_id,'DEL',current_date,old_row.emp_name););

Insert Trigger:
-------------

CREATE TRIGGER x1_i_t
AFTER INSERT
ON x1
REFERENCING NEW AS new_row
FOR EACH ROW
(INSERT INTO x1_audit VALUES(new_row.emp_id,'INS',current_date,new_row.emp_name););

Please advice.
Thanks,

Vik