BwhCbeKJ

1CREATE OR REPLACE TRIGGER MYTABLE_TRG 
2BEFORE INSERT ON MYTABLE 
3FOR EACH ROW 
4BEGIN 
5 select MYTABLE_SEQ.nextval into :new.id from dual; 
6END;
7	
8Error(2,52): PLS-00049: bad bind variable 'NEW.ID'
9	
10SQL> create sequence mytable_seq;
11
12Sequence created.
13
14SQL> create table mytable (id number);
15
16Table created.
17
18SQL> CREATE OR REPLACE TRIGGER MYTABLE_TRG
19  2  BEFORE INSERT ON MYTABLE
20  3  FOR EACH ROW
21  4  BEGIN
22  5   select MYTABLE_SEQ.nextval into :new.id from dual;
23  6  END;
24  7  /
25
26Trigger created.
27	
28--START -- CREATE A SEQUENCE
29  /*
30  create table "TBL_NAME" (
31     "TBL_NAME_ID"      number(19,0) NOT NULL 
32    , ... 
33  */  
34  --------------------------------------------------------
35  --  drop the sequence if it exists
36  -- select * from user_sequences ; 
37  --------------------------------------------------------
38  declare
39     c int;
40  begin
41     select count(*) into c from user_sequences 
42      where SEQUENCE_NAME = upper('SEQ_TBL_NAME');
43     if c = 1 then
44        execute immediate 'DROP SEQUENCE SEQ_TBL_NAME';
45     end if;
46  end;
47  /
48
49  CREATE SEQUENCE  "SEQ_TBL_NAME"  
50  MINVALUE 1 MAXVALUE 999999999999999999999999999 
51  INCREMENT BY 1 START WITH 1 
52  CACHE 20 NOORDER  NOCYCLE ;
53
54
55  -- CREATE  
56  CREATE OR REPLACE TRIGGER "TRG_TBL_NAME" 
57  BEFORE INSERT
58  ON "TBL_NAME"
59  REFERENCING NEW AS New OLD AS Old
60  FOR EACH ROW
61  DECLARE
62  tmpVar NUMBER;
63
64  BEGIN
65     tmpVar := 1 ;
66
67     SELECT SEQ_TBL_NAME.NEXTVAL INTO tmpVar FROM dual;
68     :NEW.TBL_NAME_ID := tmpVar;
69
70  END TRG_TBL_NAME; 
71  /
72  ALTER TRIGGER "TRG_TBL_NAME" ENABLE;
73  -- STOP  -- CREATE THE TRIGGER