OraFAQ Forum: SQL & PL/SQL » Create Trigger not working for autoincrement

Home » SQL & PL/SQL » SQL & PL/SQL » Create Trigger not working for autoincrement (oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create Trigger not working for autoincrement [message #461052]

Wed, 16 June 2010 05:38

mamathakv
Messages: 6
Registered: June 2010
Location: bangalore

Junior Member

Step1

CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Step2
CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Method1 Follow Step1 and Step2 and create a Trigger as below :

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/

Method 2

Follow Step1 and Step2 and directly have an insert statement as below:

INSERT INTO test (id, name) VALUES (test_sequence.nextval , 'Jon343');

Method 1, the Trigger is not getting created, however by following Method 2, I am able to generate auto-increment number.

Please advise.

Report message to a moderator

Re: Create Trigger not working for autoincrement [message #461054 is a reply to message #461052]

Wed, 16 June 2010 05:44

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

So what's your question? What is not working?

SY.

Report message to a moderator

Re: Create Trigger not working for autoincrement [message #461055 is a reply to message #461052]

Wed, 16 June 2010 05:46

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Quote:

the Trigger is not getting created,

What error message do you get?

Report message to a moderator

Re: Create Trigger not working for autoincrement [message #461057 is a reply to message #461052]

Wed, 16 June 2010 05:47

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

I did not find any problem when I ran your trigger code.

CREATE TRIGGER your_trigger
before INSERT ON your_table
FOR each row
begin
  SELECT test_seq.NEXTVAL INTO :new.id FROM dual;
end;

Regards
Ved

Report message to a moderator

Re: Create Trigger not working for autoincrement [message #461058 is a reply to message #461055]

Wed, 16 June 2010 05:48

mamathakv
Messages: 6
Registered: June 2010
Location: bangalore

Junior Member

Using Method1, I try to create trigger using TOAD, There is no Trigger that is created. No error found. Simply there is no effect.

I tried this select * from user_triggers
No records found..

Report message to a moderator

Re: Create Trigger not working for autoincrement [message #461059 is a reply to message #461052]

Wed, 16 June 2010 05:49

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Are you or are you working with "manjukmoorthi" as you post closed questions from same location.

Regards
Michel

[Updated on: Wed, 16 June 2010 05:49]

Report message to a moderator

Re: Create Trigger not working for autoincrement [message #461061 is a reply to message #461059]

Wed, 16 June 2010 06:00

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

Quote:

Using Method1, I try to create trigger using TOAD, There is no Trigger that is created. No error found. Simply there is no effect.

Well TOAD is a bit stupid when it comes to that, you have to "mark" the whole "trigger block" and chose to execute it as a script.

Report message to a moderator

Re: Create Trigger not working for autoincrement [message #461065 is a reply to message #461061]

Wed, 16 June 2010 06:15

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Well TOAD is a bit stupid when it comes to that

Not just for that. It just requires high level skilled people to work with.

Regards
Michel

Report message to a moderator

Re: Create Trigger not working for autoincrement [message #461067 is a reply to message #461052]

Wed, 16 June 2010 06:24

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

Report message to a moderator

Previous Topic:	Invalid results when including date with null value in select list
Next Topic:	Query ...

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 07 19:43:36 CDT 2024