| USING A SEQUENCE IN A TRIGGER [message #40781] |
Wed, 06 November 2002 09:37  |
Adi
Messages: 38
Registered: May 2002
|
Member |
|
|
Hi all
I am trying to insert a Sequence value into a field, all this inside my Trigger. I am relatively new to this field, so Please correct any mistakes in the Trigger below:
CREATE OR REPLACE TRIGGER MY_TRIGGER
BEFORE INSERT
ON SCHEMA.TABLE1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
i NUMBER;
BEGIN
IF INSERTING THEN
:new.ID_NUM := (select SCHEMA.ID_SEQ.NEXTVAL into :i from dual);
:new.CREATED_DATE := SYSDATE;
END IF;
END;
I also tried this:
:new.ID_NUM := SCHEMA.ID_SEQ.NEXTVAL
but the error says 'Sequence not allowed in this context'
Thanks
ADI
|
|
|
|
| Re: USING A SEQUENCE IN A TRIGGER [message #40783 is a reply to message #40781] |
Wed, 06 November 2002 09:48  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
create table tab1 ( col1 number primary key, col2 number );
create sequence tab1_seq;
-- Before Insert for Each Row
CREATE OR REPLACE TRIGGER tab1_bier
BEFORE INSERT
ON tab1
FOR EACH ROW
BEGIN
IF (:NEW.col1 IS NULL)
THEN
SELECT tab1_seq.NEXTVAL
INTO :NEW.col1
FROM DUAL;
END IF;
END;
/
insert into tab1 (col2) values (33);
insert into tab1 (col2) values (99);
select * from tab1;
1 33
2 99
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
