CODEWITHSUNDEEP
sqloracle-databaseplsqlviewtriggers
bcdyzi
bcdyzi

Reputation: 35

Trigger on Views in PL/SQL

I want to write a trigger in PL/SQL. My first aim is to compare two time_stamp data type (A and B). If one of these is bigger than another, for instance A>B, i will update columns on another table. Trigger that i try to write is like below.

CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE ON views
FOR EACH ROW
  DECLARE
    A views.X%TYPE;
    B views.Y%TYPE;
    C views.Z%TYPE;
BEGIN
  SELECT X, Y, Z INTO A, B, C FROM views;
  IF A>B THEN
  update another_table set D=' ' and E='UNRESOLVED' where column1=A;
  ELSE
  dbms_output.put_line('ABC: ' || A || ' < ' || 'CDE' || B);
  END IF;
END;

If i execute this trigger, i'm getting error like below.

Error report: ORA-25001: kan inte skapa den här triggertypen i den här typen av vy 25001. 00000 - "cannot create this trigger type on views" *Cause: Only INSTEAD OF triggers can be created on a view. *Action: Change the trigger type to INSTEAD OF.

Thanks in advance for your help.

Upvotes: 2

Views: 4004

Answers (1)

wolφi
wolφi

Reputation: 8361

You're nearly there. This is only a syntactic confusion. You cannot create a trigger that fires BEFORE or AFTER an insert or update or delete of a view, but you can create a trigger that fires INSTEAD OF an insert or update or delete:

TABLE BEFORE / AFTER insert or update or delete VIEW INSTEAD OF insert or update or delete

And, as @Belayer writes, you don't (and shouldn't) use SELECT, use the automatically prepared record called :new for the new values during insert or update, or the record ':old' for the old values during update or delete.

Your trigger would look something like:

CREATE OR REPLACE TRIGGER views_tr
  INSTEAD OF INSERT OR UPDATE OR DELETE ON views
  FOR EACH ROW
BEGIN
  IF :new.x > :new.y THEN
    UPDATE another_table SET D=' ', ... WHERE column1 = :new.x;
  ELSE
    dbms_output.put_line('ABC: ' || :new.x || ' < ' || 'CDE' || :new.y);
  END IF;
END views_tr;
/

Upvotes: 2

Related Questions