But when the first trigger is created, I get an error of compilation and everything stop there. (Which is correct)
When I look at the trigger body, the code inside the trigger, I see the code of the trigger (BEGIN … END;) and I also see the command that create the second sequence, the second trigger, the third sequence, the… and so on.
Just like that:
TRIGGER BODY OF FIRST TRIGGER
——-STARTS HERE——–
BEGIN
SELECT FirstSeq.nextval INTO :new.FirstId FROM dual;
END;
— These three previous commands are correct (BEGIN,
— SELECT, END), but what follows should not be in the
— trigger body.
— 2 SecondSeq;
DROP TABLE TblSecond;
CREATE TABLE TblSecond( SecondId number );
DROP SEQUENCE SecondSeq;
CREATE SEQUENCE SecondSeq;
DROP TRIGGER SecondTrig;
CREATE TRIGGER SecondTrig
BEFORE INSERT ON TblSecond
FOR EACH ROW
BEGIN
SELECT SecondSeq.nextval INTO :new.SecondId FROM dual;
END;
———STOPS HERE—————-
I added some table creation to complete the example. So, the code between –STARTS HERE– and –STOPS HERE– is in the trigger body.
Here’s the code that I execute in SQL*Plus Worksheet.
— 1 FirstSeq;
DROP TABLE TblFirst;
CREATE TABLE TblFirst( FirstId number );
DROP SEQUENCE FirstSeq;
CREATE SEQUENCE FirstSeq;
DROP TRIGGER FirstTrig;
CREATE TRIGGER FirstTrig
BEFORE INSERT ON TblFirst
FOR EACH ROW
BEGIN
SELECT FirstSeq.nextval INTO :new.FirstId FROM dual;
END;
— 2 SecondSeq;
DROP TABLE TblSecond;
CREATE TABLE TblSecond( SecondId number );
DROP SEQUENCE SecondSeq;
CREATE SEQUENCE SecondSeq;
DROP TRIGGER SecondTrig;
CREATE TRIGGER SecondTrig
BEFORE INSERT ON TblSecond
FOR EACH ROW
BEGIN
SELECT SecondSeq.nextval INTO :new.SecondId FROM dual;
END;
I hope that you follow me.
(I’m sorry, the previous message was not clear enough to understand what was inside the trigger body)