[FIXED] How to fix basic trigger not triggered in SQL Oracle

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)