Help writing trigger function 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, but it won't say what the error is! 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;

 

[Azita79]

after running the trigger, type in :
show error
and it will show you the line that is causing the compilation error.

I hope this helps


Azita

 

[tlbroadbent]

I suggest that you post your question in an Oracle forum as this is a Microsoft SQL Server forum. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm