Issue
I've been trying to get this trigger to work but for some reason it does not even activate before I update/insert things in the table. The aim of this trigger (and I'm required to do this through triggers) is to ensure that all entries in the DISCS table have either 'L' or 'S' in the FORMAT attribute. If not, the entry should not be inserted/updated.
I've tried to add a line to check if the trigger was being activated at all (the "buenos dias" line) but it seems it isn't for some reason. I'm just a beginner with triggers and I'm sure the error is really dumb but I just can't find it.
The code for the trigger is:
CREATE OR REPLACE TRIGGER FORMAT_T
BEFORE INSERT OR UPDATE OF FORMAT
ON DISCS
FOR EACH ROW
DECLARE
bad_format EXCEPTION;
BEGIN
DBMS_OUTPUT.PUT_LINE('Buenos dias.');
IF :NEW.FORMAT != 'S' AND :NEW.FORMAT != 'L' THEN
RAISE bad_format;
END IF;
EXCEPTION
WHEN bad_format THEN
DBMS_OUTPUT.PUT_LINE('Bad format, could not insert/update.');
END FORMAT_T;
Once I compile the trigger without problems, I try to update the following row (using the ID of the first row just to be able to test it easily):
UPDATE DISCS
SET FORMAT = 'W'
WHERE ID = 1;
This should return an error since the format I'm updating it to is not 'L' nor 'S', but instead the row is just updated without problems and without the "buenos dias" line even popping up. There are no errors in the user_errors table either.
Thank you for your help in advance.
Solution
Your trigger code as posted works just fine. The real problem is revealed by this:
without the "buenos dias" line even popping up
Clearly you're running in an environment in which DBMS_OUTPUT is disabled. So the UPDATE fails as expected, you just don't see the message. We can demonstrate this easily by removing the EXCEPTION block from your trigger code, as this SQL Fiddle demonstrates.
Enabling DBMS_OUTPUT depends on your execution environment. In SQL*Plus run set serveroutput on
. In Oracle SQL Developer choose View > Dbms_Output and set the connection. In PL/SQL Developer enable DBMS_OUTPUT on the SQL worksheet. And so on.
Incidentally, a trigger is the wrong way to enforce this rule. The correct approach is to define a check constraint on the column.
alter table disks add constraint disc_format_ck
check (format in ('S', 'L'));
Answered By - APC Answer Checked By - Candace Johnson (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.