Newbie trigger help please 1

[beredon]

OK, first of all, here is the trigger that I have been trying to create:

CREATE OR REPLACE TRIGGER inst_date_today
	BEFORE INSERT OR UPDATE ON installation

DECLARE

	todays_date     DATE := SYSDATE;
	inst_date_error EXCEPTION;

BEGIN

	IF :NEW.instdate != todays_date THEN
		RAISE inst_date_error;
	END IF;

EXCEPTION

	WHEN inst_date_error THEN
		DBMS_OUTPUT.PUT_LINE('The installation date must be the current date.');

END;

Here's the installation table:

 Name      Null?    Type
 --------- -------- -----------
 PACKID    NOT NULL VARCHAR2(4)
 TAGNUM    NOT NULL NUMBER(5)
 INSTDATE           DATE
 INSTCOST           NUMBER(5,2)

Now, I've been trying to get this working for hours, and I just can't get it! Basically, I want the trigger to compare the date in a record being inserted with the system date. If they don't match, it'll throw the "inst_date_error" exception. Otherwise, its business as usual.

No matter what I try, it tells me that I can't use the NEW or OLD references. But I can't for the life of me work out how else I can reference the new instdate without using NEW.

So, can anyone see what I'm doing wrong?

I am new to triggers, so please go easy on me.

Thanks.

 

[sem]

To use :new and ld your triger should be row-level. Just add FOR EACH ROW statement. Then don't forget about TIME part: as I may assume you need to compare days, not full datetimes. If this is the case add TRUNC.

Regards, Dima

http://seminihin.narod.ru

 

[beredon]

Ahhhh!

Yeah that worked a gem.

Cheers!