| working with triggers, error substitute number for '.' [message #38829] |
Fri, 17 May 2002 16:28  |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
I am working on this business rule for a trigger:
"The employer table should only contain exactly one PRESIDENT."
Here is the code:
CREATE OR REPLACE TRIGGER ONE_PRESIDENT
BEFORE INSERT OR UPDATE OR DELETE ON EMP
FOR EACH ROW
BEGIN
v_one_president NUMBER := NULL;
SELECT COUNT(*)
INTO v_one_president
FROM EMP
WHERE JOB = 'PRESIDENT';
IF UPDATING THEN
IF :OLD.JOB = 'PRESIDENT' AND :NEW.JOB <> 'PRESIDENT' THEN --DEMOTED
v_one_president := v_one_president - 1;
ELSIF :OLD.JOB <> 'PRESIDENT' AND :NEW.JOB = 'PRESIDENT' --PROMOTED
IF v_one_president IS NOT NULL THEN
--THERE IS A PRESIDENT ALREADY
RAISE_APPLICATION_ERROR(-20001, 'President title already exists');
ELSE
v_one_president := v_one_president + 1;
END IF;
END IF;
END IF;
IF INSERTING THEN
IF :NEW.JOB = 'PRESIDENT' THEN
IF v_one_president IS NOT NULL THEN
--THERE IS A PRESIDENT ALREADY
RAISE_APPLICATION_ERROR(-20001, 'President title already exists');
ELSE
v_one_president := v_one_president + 1;
END IF;
END IF;
END IF;
IF DELETING THEN
IF :OLD.JOB = 'PRESIDENT' THEN
v_one_president := v_one_president - 1;
END IF;
END IF;
END;
/
Here is the error:
Errors for TRIGGER ONE_PRESIDENT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/19 PLS-00103: Encountered the symbol "NUMBER" when expecting one of
the following:
:= . ( @ % ;
The symbol "." was substituted for "NUMBER" to continue.
13/8 PLS-00103: Encountered the symbol "IF" when expecting one of the
following:
. ( * @ % & - + / at mod rem then <an exponent (**)> and or
||
The symbol "then" was substituted for "IF" to continue.
--I OBVIOUSLY NEED SOME KIND OF COUNTER??
--BUT I DON'T REALLY KNOW HOW I SHOULD APPROACH IT.
|
|
|
|
| Re: working with triggers, error substitute number for '.' [message #38831 is a reply to message #38829] |
Sun, 19 May 2002 10:19  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
CREATE OR REPLACE TRIGGER ONE_PRESIDENT
BEFORE INSERT OR UPDATE OR DELETE ON Emp
FOR EACH ROW
declare
v_one_president NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_one_president
FROM EMP
WHERE JOB = 'PRESIDENT';
IF UPDATING THEN
IF :OLD.JOB = 'PRESIDENT' AND :NEW.JOB <> 'PRESIDENT' THEN --DEMOTED
v_one_president := v_one_president - 1;
ELSIF :OLD.JOB <> 'PRESIDENT' AND :NEW.JOB = 'PRESIDENT' THEN
--PROMOTED
IF v_one_president IS NOT NULL THEN
--THERE IS A PRESIDENT ALREADY
RAISE_APPLICATION_ERROR(-20001, 'President title already exists');
ELSE
v_one_president := v_one_president + 1;
END IF;
END IF;
END IF;
IF INSERTING THEN
IF :NEW.JOB = 'PRESIDENT' THEN
IF v_one_president IS NOT NULL THEN
--THERE IS A PRESIDENT ALREADY
RAISE_APPLICATION_ERROR(-20001, 'President title already exists');
ELSE
v_one_president := v_one_president + 1;
END IF;
END IF;
END IF;
IF DELETING THEN
IF :OLD.JOB = 'PRESIDENT' THEN
v_one_president := v_one_president - 1;
END IF;
END IF;
END;
/
trigger created
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
