Home » SQL & PL/SQL » SQL & PL/SQL » Creating more than 1 trigger
Creating more than 1 trigger [message #438524] Sun, 10 January 2010 09:13 Go to next message
SCOTLANDS_LION
Messages: 9
Registered: January 2010
Location: Scotland
Junior Member
When you're creating triggers in oracle, how do you create more than one. I'm trying to create 3 triggers in a sql script but i keep getting the error:

ERROR at line 7: PLS-00103: Encountered the symbol "CREATE"

The script for creating the triggers is:

CREATE OR REPLACE TRIGGER studentdata
BEFORE INSERT ON studentdata
FOR EACH ROW
BEGIN
SELECT studentdata_StudentID_seq.nextval
INTO :NEW.StudentID
FROM dual;
END;

CREATE OR REPLACE TRIGGER resultdata
BEFORE INSERT ON resultdata
FOR EACH ROW
BEGIN
SELECT resultdata_ResultID_seq.nextval
INTO :NEW.ResultID
FROM dual;
END;

CREATE OR REPLACE TRIGGER moduledata
BEFORE INSERT ON studentdata
FOR EACH ROW
BEGIN
SELECT moduledata_ModID_seq.nextval
INTO :NEW.ModID
FROM dual;
END;

I'm very new to sql and dont have much experience in it. Any help would be much appreciated, Thanks.
Re: Creating more than 1 trigger [message #438525 is a reply to message #438524] Sun, 10 January 2010 09:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you use SQL*Plus (and you should), you have not the problem.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Creating more than 1 trigger [message #438526 is a reply to message #438525] Sun, 10 January 2010 09:42 Go to previous messageGo to next message
SCOTLANDS_LION
Messages: 9
Registered: January 2010
Location: Scotland
Junior Member
Michel Cadot wrote on Sun, 10 January 2010 15:35
If you use SQL*Plus (and you should), you have not the problem.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel



I use oracle database express edition.

I dont know if this uses sql*plus. As i said, im new to this and learning everyday. Could you explain why i am getting this error and any tips on what i am doing wrong? Thanks

[Updated on: Sun, 10 January 2010 09:42]

Report message to a moderator

Re: Creating more than 1 trigger [message #438527 is a reply to message #438524] Sun, 10 January 2010 09:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
CREATE OR REPLACE TRIGGER studentdata
  BEFORE INSERT ON studentdata
  FOR EACH ROW
BEGIN
  SELECT studentdata_studentid_seq.nextval
  INTO   :NEW.studentid
  FROM   dual;
END;
/

CREATE OR REPLACE TRIGGER resultdata
  BEFORE INSERT ON resultdata
  FOR EACH ROW
BEGIN
  SELECT resultdata_resultid_seq.nextval
  INTO   :NEW.resultid
  FROM   dual;
END;
/

CREATE OR REPLACE TRIGGER moduledata
  BEFORE INSERT ON studentdata
  FOR EACH ROW
BEGIN
  SELECT moduledata_modid_seq.nextval
  INTO   :NEW.modid
  FROM   dual;
END;
/ 
Re: Creating more than 1 trigger [message #438528 is a reply to message #438524] Sun, 10 January 2010 10:17 Go to previous messageGo to next message
SCOTLANDS_LION
Messages: 9
Registered: January 2010
Location: Scotland
Junior Member
Thanks Swan.

It seems everytime i ask a question you answer the perfect answer, no messing about, no dancing round about it, just straight to the point.

Thats 2 pints i owe you now lol.

Cheers.
Re: Creating more than 1 trigger [message #438529 is a reply to message #438524] Sun, 10 January 2010 10:29 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
Glad to see that you get your answer.

But I would like to point out that Michel Cadot has a very valid point. You should use sqlplus, the command line interface, instead of GUI-based tools. GUI-based tools hide a lot of information that you do have to enter if you use a command line interface.

GUI based tools don't give half the learning experience as sqlplus. So if you want an even more steep learning curve I encourage you to use sqlplus.
Re: Creating more than 1 trigger [message #438530 is a reply to message #438529] Sun, 10 January 2010 10:44 Go to previous messageGo to next message
SCOTLANDS_LION
Messages: 9
Registered: January 2010
Location: Scotland
Junior Member
martijn wrote on Sun, 10 January 2010 16:29
Glad to see that you get your answer.

But I would like to point out that Michel Cadot has a very valid point. You should use sqlplus, the command line interface, instead of GUI-based tools. GUI-based tools hide a lot of information that you do have to enter if you use a command line interface.

GUI based tools don't give half the learning experience as sqlplus. So if you want an even more steep learning curve I encourage you to use sqlplus.



I'm using notepad and uploading it to Oracle Express as a script. I'll keep sql plus in mind though, ive not got much free time to have a look at it at the moment.
Re: Creating more than 1 trigger [message #438543 is a reply to message #438530] Sun, 10 January 2010 15:30 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
hehe you entered a very interesting and fun arena...so take your time, and you will have a lot of fun learning some very interesting concepts.
Re: Creating more than 1 trigger [message #438567 is a reply to message #438529] Sun, 10 January 2010 23:56 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
martijn wrote on Sun, 10 January 2010 17:29
Glad to see that you get your answer.

But I would like to point out that Michel Cadot has a very valid point. You should use sqlplus, the command line interface, instead of GUI-based tools. GUI-based tools hide a lot of information that you do have to enter if you use a command line interface.

GUI based tools don't give half the learning experience as sqlplus. So if you want an even more steep learning curve I encourage you to use sqlplus.

Nothing in this whole thread says the original poster does NOT use sqlplus. The problem was not in a gui-tool or whatever, the problem was that the script was not a valid SQLPLUS script, because the trailing slashes were missing after each individual command (as pointed out by BlackSwan)
Re: Creating more than 1 trigger [message #438588 is a reply to message #438567] Mon, 11 January 2010 02:08 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
Oh....I'm very sorry that I wrote it down then.
I don't want to be to helpfull.

(The OP did mention that he uses notepad, although it was after my remark)
Re: Creating more than 1 trigger [message #438623 is a reply to message #438588] Mon, 11 January 2010 04:45 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
True, but using notepad hints very strongly towards using sqlplus!
Previous Topic: Email Notification
Next Topic: XMLTYPE to CLOB
Goto Forum:
  


Current Time: Sat Feb 15 20:36:57 CST 2025