Home » SQL & PL/SQL » SQL & PL/SQL » trigger problem
trigger problem [message #10468] Sun, 25 January 2004 03:00 Go to next message
antreas
Messages: 5
Registered: January 2004
Junior Member
I have the following two tables and one trigger.

----------------------------------------------------------------------

CREATE TABLE CAR

(BRAND CHAR(10) NOT NULL,

MODEL CHAR(10) NOT NULL,

CUBICS INT,

MADEYEAR INT NOT NULL,

PRIMARY KEY(BRAND,MODEL));

CREATE TABLE VEHICLE

(LICENCENUMBER CHAR(7) NOT NULL,

BRAND CHAR(10) NOT NULL,

MODEL CHAR(10) NOT NULL,

COLOR VARCHAR(15),

YEAR INT NOT NULL,

PRIMARY KEY(LICENCENUMBER),

FOREIGN KEY(BRAND,MODEL) REFERENCES CAR(BRAND,MODEL)

ON DELETE SET NULL);

 

CREATE OR REPLACE TRIGGER LICENCENUMBERCHECK

AFTER INSERT OR UPDATE ON VEHICLE

FOR EACH ROW

declare N_MADEYEAR CAR.MADEYEAR%TYPE;

BEGIN

SELECT MADEYEAR INTO N_MADEYEAR FROM CAR,VEHICLE

WHERE VEHICLE.BRAND=CAR.BRAND

AND VEHICLE.MODEL=CAR.MODEL;

IF(:new.YEAR < N_MADEYEAR ) THEN

RAISE_APPLICATION_ERROR(-20000,'NO MIKROTERO ALLOWED');

END IF;

exception

when others then dbms_output.put_line(sqlerrm);

END;

.

run;

----------------------------------------------------------------------

MADEYEAR is the year when a car was made and YEAR  is the year when the licence for a car was issued.What i want to make sure is that no vehicle will have a licence number which was issued before the year that model was made,which would be wrong.I tried this trigger.It compiled right and was created but it doesn't do anything.I tried everything i could but nothing.If somebody can help me i would totally appreciate it.

Thanx.
Re: trigger problem [message #10469 is a reply to message #10468] Sun, 25 January 2004 05:33 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
A VEHICLE is a type of CAR? What about trucks, buses etc?

Also the CHAR datatype is essentially obsolete and should be avoided unless you specifically want the blank-padding that comes with it (trust me, you don't).

I won't even ask what MIKROTERO is.

In your trigger you were raising an exception via RAISE_APPLICATION_ERROR, but then immediately handling it by displaying some text with DBMS_OUTPUT - which only does anything within tools such as SQL*Plus that can display it, if it's enabled via SET SERVEROUTPUT ON or equivalent. If you remove the exception handler you would then see the actual SQLERRM, which is a mutating table error caused by an attempt to query the trigger's own table in mid-update, while the values are still indeterminate (e.g. there might be other triggers yet to run, in an unknown order).

Try this:
CREATE OR REPLACE TRIGGER licencenumbercheck 
BEFORE INSERT OR UPDATE ON vehicle 
FOR EACH ROW 
DECLARE
	v_madeyear car.madeyear%TYPE; 
BEGIN 
	SELECT madeyear
	INTO   v_madeyear
	FROM   car
	WHERE  brand = :new.brand
	AND    model = :new.model;

	IF :new.year < v_madeyear THEN 
		RAISE_APPLICATION_ERROR
		( -20000
		,'NO MIKROTERO ALLOWED' ); 
	END IF;
END;
/

SQL> INSERT ALL            
  2  INTO car VALUES ('BMW','540','4400', 1998)
  3  INTO car VALUES ('BMW','535','3500', 1998)
  4  INTO car VALUES ('FORD','CORTINA',1600,1967)
  5  SELECT * FROM dual;

3 rows created.

SQL> commit;

Commit complete.

SQL> INSERT INTO vehicle VALUES
  2* ( 'VMC363M', 'BMW', '540', 'COSMOS BLACK', 1931 );

INSERT INTO vehicle VALUES
            *
ERROR at line 1:
ORA-20000: NO MIKROTERO ALLOWED
ORA-06512: at "WILLIAMR.LICENCENUMBERCHECK", line 10
ORA-04088: error during execution of trigger 'WILLIAMR.LICENCENUMBERCHECK'

SQL> INSERT INTO vehicle VALUES
  2* ( 'VMC363M', 'BMW', '540', 'COSMOS BLACK', 1999 );

1 row created.

Mikrotero is now prevented.
Re: trigger problem [message #10470 is a reply to message #10469] Sun, 25 January 2004 06:31 Go to previous message
antreas
Messages: 5
Registered: January 2004
Junior Member
Thanx for the feedback.MIKROTERO is LESS in greek.I didn't translate it when i sent the question cause i didn't see it.I will change char to varchar.Vehicle represents some car,i don't have anything special for buses or trucks.I just use this table with some other tables for owners in my database.Thanx again.
Previous Topic: Conditional Insert???
Next Topic: autoexec.bat
Goto Forum:
  


Current Time: Fri Mar 29 10:56:53 CDT 2024