Begynder vanskeligheder med trigger

Prøv noget ala:


CREATE TABLE STAFF
(
  FIRSTNAME      VARCHAR2(30 BYTE),
  LASTNAME      VARCHAR2(30 BYTE),
  TITLE          VARCHAR2(30 BYTE),
  CURRENTSALARY  NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE TABLE SALARYSCALE
(
  MINSALARY  NUMBER,
  MAXSALARY  NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;


INSERT INTO SALARYSCALE ( MINSALARY, MAXSALARY ) VALUES (
12000, 90000);
COMMIT;



CREATE OR REPLACE TRIGGER salaryCheck
BEFORE INSERT OR UPDATE OF currentSalary ON Staff
FOR EACH ROW
DECLARE

low_salary EXCEPTION;
high_salary EXCEPTION;
minsal_temp number;
maxsal_temp number;

BEGIN

select minsalary into minsal_temp from salaryscale;
select maxsalary into maxsal_temp from salaryscale;

IF :NEW.currentSalary < minsal_temp
then        RAISE low_salary;
    END IF;

IF :NEW.currentSalary > maxsal_temp
then        RAISE high_salary;
END IF;

   
    EXCEPTION
    when low_salary THEN
  raise_application_error(-20000, 'Invalid salary - Lowest salary is minimum '|| minsal_temp);
      WHEN high_salary THEN
    raise_application_error(-20000, 'Invalid salary - Highest salary is maximum '|| maxsal_temp);
END;


--END salaryCheck;
/


SQL> INSERT INTO STAFF ( FIRSTNAME, LASTNAME, TITLE, CURRENTSALARY ) VALUES (
  2  'John', 'Denver', 'Singer', 10000);
INSERT INTO STAFF ( FIRSTNAME, LASTNAME, TITLE, CURRENTSALARY ) VALUES (
            *
ERROR at line 1:
ORA-20000: Invalid salary - Lowest salary is minimum 12000
ORA-06512: at "SALARYCHECK", line 24
ORA-04088: error during execution of trigger 'SALARYCHECK'


SQL>
SQL> INSERT INTO STAFF ( FIRSTNAME, LASTNAME, TITLE, CURRENTSALARY ) VALUES (
  2  'Michael', 'Laudrup', 'Singer', 91000);
INSERT INTO STAFF ( FIRSTNAME, LASTNAME, TITLE, CURRENTSALARY ) VALUES (
            *
ERROR at line 1:
ORA-20000: Invalid salary - Highest salary is maximum 90000
ORA-06512: at "SALARYCHECK", line 27
ORA-04088: error during execution of trigger 'SALARYCHECK'


SQL>

 
   
Men som man ser vil du også få en ORA-4088 - måske du er ligeglad. Ellers se her:

Problem Description
-------------------

You are creating a trigger that includes an exception handling block.  You wish
to raise a user defined error when a certain condition is met within the trigger
body using keyword RAISE.  Inside your error handling block you also include a
call to RAISE_APPLICATION_ERROR.

Consider this code example --

create table tmp (col1 char(40));
create table violations (col1 varchar2(30));

CREATE OR REPLACE TRIGGER DEMO_RULE_001
BEFORE INSERT OR UPDATE ON TMP
FOR EACH ROW
DECLARE
  RULE_001 EXCEPTION;
BEGIN
  IF :NEW.col1 = 'mike' THEN
  dbms_output.put_line(:new.col1);
  INSERT INTO VIOLATIONS values ('violation logged');
  -- Raise rule
  RAISE RULE_001;
  END IF;
EXCEPTION
WHEN RULE_001 THEN 
  RAISE_APPLICATION_ERROR (-20001,'Guideline Violation, Rule-001.');
END;

When this trigger is executed, you receive the ora-4088 and ora-6512 errors.
  ORA-04088: error during execution of trigger 'SCOTT.DEMO_RULE_001'


Solution Description
--------------------

You cannot use both RAISE, within the execution block of a trigger, and
RAISE_APPLICATION_ERROR, within the exception block. 


Explanation
-----------

RAISE forces execution to move into the exception block.
RAISE_APPLICATION_ERROR, within the exception block, terminates the program.
If the trigger body does not complete, the triggering SQL statement and any
SQL statements within the trigger body are rolled back. Thus, execution
completes unsuccessfully with a runtime error and it appears as if none of
the code within the trigger body gets executed.

Consider this corrected code --

CREATE OR REPLACE TRIGGER DEMO_RULE_001
BEFORE INSERT OR UPDATE ON TMP
FOR EACH ROW
DECLARE
  RULE_001 EXCEPTION;
BEGIN
  IF :NEW.col1 = 'mike' THEN
  dbms_output.put_line(:new.col1);
  INSERT INTO VIOLATIONS values ('violation logged');
  -- Raise rule
  RAISE RULE_001;
  END IF;
EXCEPTION
WHEN RULE_001 THEN 
  --raise_application_error(-20001, 'Guideline Violation, Rule-001.');
  dbms_output.put_line('Guideline Violation, Rule-001.');
END;

References
----------

PLSQL Users Guide and Reference, (8.1.6), Ch. 6, (Part Number A77069-01)
Application Developer's Guide - Fundamentals, (8.1.5), Ch. 13, (Part Number A68003-01)

Det virkede ikke helt, men der skulle ikke meget til så gjorde det, så point til dig - tak for det meget fyldige svar

Helt ok - havde kopieret Singer ned til Michael Laudrup :-) Var det der den fejlede? ;-)

Vis mig dine tabeller og det du har næste gang - så er det nemmere at gennemskue. Forstod ikke helt din forklaring - men gættede til at det var noget ala ovenstående.

Bare du er videre - det er det vigtigste.

Husk Karma :-)