Help creating SQL trigger in Oracle

[PJYelton]

Hi, I am very new to SQL and am trying to create a trigger in Oracle but keep getting an error saying trigger compilation error. When I type show errors it just says encountered CREATE. This is the relavent code that I have and I'm unsure of what is wrong:

CREATE TABLE scores
(
date_Of_call DATE NOT NULL,
time_Of_call VARCHAR2(5) NOT NULL,
agent_ID VARCHAR2(5) NOT NULL,
phone_number VARCHAR2(12) NOT NULL,
call_Type VARCHAR2(2) NOT NULL,
caring NUMBER(1) NOT NULL,
communication NUMBER(1) NOT NULL,
commitment NUMBER(1) NOT NULL,
competence NUMBER(1) NOT NULL,
compliance NUMBER(1),
creativity NUMBER(1),
CONSTRAINT scores_pk PRIMARY KEY (date_of_call,time_of_call),
CONSTRAINT scores_FK FOREIGN KEY (agent_ID) REFERENCES CSR (agent_ID)
);

CREATE OR REPLACE TRIGGER sometimes_null_trigger1 BEFORE UPDATE OR INSERT ON scores
FOR EACH ROW
BEGIN
IF new.call_type = 'RA' OR :new.call_type = 'I' OR :new.call_type = 'C')
AND new.compliance IS NULL OR :new.creativity IS NOT NULL)
THEN raise_application_error(-20512,'You cannot have compliance NULL or creativity not null with this call type');
END IF;
END;

CREATE OR REPLACE TRIGGER sometimes_null_trigger2 BEFORE UPDATE OR INSERT ON scores
FOR EACH ROW
BEGIN
IF new.call_type != 'RA' AND :new.call_type != 'I' AND :new.call_type != 'C')
AND new.compliance IS NOT NULL)
THEN raise_application_error(-20512,'You cannot have compliance with this call type');
END IF;
END;

 

[topher0303]

Try putting a slash on the line after each trigger:

CREATE OR REPLACE TRIGGER sometimes_null_trigger1 BEFORE UPDATE OR INSERT ON scores
FOR EACH ROW
BEGIN
IF new.call_type = 'RA' OR :new.call_type = 'I' OR :new.call_type = 'C')
AND new.compliance IS NULL OR :new.creativity IS NOT NULL)
THEN raise_application_error(-20512,'You cannot have compliance NULL or creativity not null with this call type');
END IF;
END;
/

CREATE OR REPLACE TRIGGER sometimes_null_trigger2 BEFORE UPDATE OR INSERT ON scores
FOR EACH ROW
BEGIN
IF new.call_type != 'RA' AND :new.call_type != 'I' AND :new.call_type != 'C')
AND new.compliance IS NOT NULL)
THEN raise_application_error(-20512,'You cannot have compliance with this call type');
END IF;
END;
/

 

[nme]

Did the previous solution work - if not, are you typing this directly into SQL*Plus?

Your code looked fine at first glance (I haven't been through it with a fined tooth comb) but I have had problems in the past with trying to do multiple tasks in SQL*Plus without saving it to a file and then using the @ command to run the file e.g.

@C:\my_sql\my_sql_filename.sql

Hope this helps - if not you could try running them one at a time to hope to narrow down the search for the faulty code.

 

[topher0303]

I have the same problems as you nme. Pasting into sqlplus doesn't seem to work as well as the @ method you specified. It seems run multiple triggers/sprocs statements together when you paste them. It then tries to run it all as one statement, which, of course errors. This could also be contengent upon the version of SQLPlus you run as well. I seem to have less trouble with sqlplus 9 than with previous versions. However, I have not confirmed this since I usually either past one at a time in or use the @ method.

Chris.

 

[PJYelton]

I am using the @ method and still getting the errors, but I am running two trigger creations at the same time along with all of my table creations. I'll try running everything one at a time tonight when I'm home to see if that will work. I'll let you know, thanks!

 

[topher0303]

did you try putting the slashes on the line after each trigger?

 

[PJYelton]

Thanks guys, I tried it with the slashes and one at a time and got it to work!

 

[karenlee]

Why isn't this documented anywhere? Anyone know? I haven't seen that in any books, and I just spent a long time banging my head against a wall trying to get my trigger to work, until I found this.