Knowledge withTriggers
From: R.Duke <*rduke_at_edcen.wrdsb.edu.on.ca>
Date: Thu, 12 Nov 1998 16:39:48 GMT
Message-ID: <364b0dd9.10629183_at_edcen>
Hello,
CREATE OR REPLACE TRIGGER PRE_UPDATE
BEFORE
INSERT OR UPDATE
ON SA_SPECIFICATION_DETAILS
FOR EACH ROW
WHEN (NEW.ATTRIBUTE_DESC IN ('ROOM LENGTH', 'ROOM HEIGHT', 'ROOM WIDTH', 'ROOM TRUE AREA', 'ROOM CAPACITY')) Declare
v_attribute_value varchar2(256) := null; Begin
IF :NEW.ATTRIBUTE_VALUE IS NOT NULL THEN
END IF;
Exception
When others then
Date: Thu, 12 Nov 1998 16:39:48 GMT
Message-ID: <364b0dd9.10629183_at_edcen>
Hello,
I have a trigger I created below that simply verifies the data entered in these fields are in fact numeric and formatted appropriately.
I would rather have these hard coded values, ('ROOM LENGTH', 'ROOM HEIGHT', ect.) come from a control table I have created as well, for better managebility. I understand that the WHEN trigger restriction can only be correlation names, not a query. Perhaps a way around this?
I appreciate any insight into this, thanx in advance.
CREATE OR REPLACE TRIGGER PRE_UPDATE
BEFORE
INSERT OR UPDATE
ON SA_SPECIFICATION_DETAILS
FOR EACH ROW
WHEN (NEW.ATTRIBUTE_DESC IN ('ROOM LENGTH', 'ROOM HEIGHT', 'ROOM WIDTH', 'ROOM TRUE AREA', 'ROOM CAPACITY')) Declare
v_attribute_value varchar2(256) := null; Begin
IF :NEW.ATTRIBUTE_VALUE IS NOT NULL THEN
IF :NEW.ATTRIBUTE_DESC = 'ROOM LENGTH' OR
:NEW.ATTRIBUTE_DESC = 'ROOM HEIGHT' OR
:NEW.ATTRIBUTE_DESC = 'ROOM WIDTH' THEN
v_attribute_value :=
to_char(to_number(:new.attribute_value, '999.99'));
END IF;
IF :NEW.ATTRIBUTE_DESC = 'ROOM TRUE AREA' OR
:NEW.ATTRIBUTE_DESC = 'ROOM CAPACITY' THEN
v_attribute_value :=
to_char(to_number(:new.attribute_value));
END IF;
END IF;
Exception
When others then
Raise_Application_Error (-20001, '['||:new.attribute_value||'] must be
numeric (999.99)');
End;
/
Received on Thu Nov 12 1998 - 17:39:48 CET