--Error working on Triggers--

Database

--Error working on Triggers--

Hi,

I am new to teradata,i created a after insert trigger,but it is giving the below error

CREATE TRIGGER testtrigger
AFTER INSERT ON inserttable
REFERENCING OLD AS o NEW AS n
FOR EACH STATEMENT
(INSERT INTO logtable VALUES(n.num););

Statement 1 - Code = 3706: se] Syntax error: expected something between ';' and the end of the request.
Output directed to answer set tab.
1 REPLY
rgs
Enthusiast

Re: --Error working on Triggers--

The syntax error could be better. On TD 12.0 I got the following error for your statement:

Failure 5423 This trigger contains an invalid REFERENCING clause.

I am not sure you want a statement trigger or a row trigger. A statement trigger only fires once at the end of your statement. A row trigger fires for each row that is changed. I’ll show both:

Statement trigger:

CREATE TRIGGER testtrigger
AFTER INSERT ON inserttable
REFERENCING NEW TABLE as n
FOR EACH STATEMENT
(INSERT INTO logtable SELECT num FROM n;);

Row trigger:

CREATE TRIGGER testtrigger
AFTER INSERT ON inserttable
REFERENCING NEW ROW AS n
FOR EACH ROW
(INSERT INTO logtable VALUES(n.num););

You can’t specify OLD for an AFTER trigger. Also in earlier versions you might have to say NEW_TABLE for the statmement trigger refrencing clause.

Note that for the statement trigger an INSERT SELECT is done to insert the whole transition table into logtable.