0

I have a table t1 with fields id, name and date. When I update the date to a certain value I want to move that entry to another table t2 (Remove it in t1 and insert it into t2). I explicitly want to do this on an UPDATE operation and not a DELETE operation.

I tried doing it with a AFTER UPDATE ON t1 trigger, however, I found out that I can't delete an entry from t1 in a trigger for t1.

Does that mean I need to do this with a procedure? If yes, how do I make a procedure run everytime I make an UPDATE of the date field to a certain value in t1?

krise
  • 485
  • 1
  • 10
  • 22
  • 3
    *" how do I make a procedure run everytime I make an UPDATE of the date field to a certain value in t1"*. You can't. What you can do is build an API over `T1` which would allow you to do this, and revoke UPDATE (INSERT, DELETE) from everybody, forcing them to call your API instead. Of course, this option is only viable in applications with a strong design principle rooted in PL/SQL logic. So it probably isn't a solution for you (just statistically, before very few applications have strong PL/SQL design cultures). – APC Aug 14 '19 at 12:03

1 Answers1

3

Create a view for table t1 and then on the view create a INSTEAD OF TRIGGER:

create view v1 as select * from t1;

CREATE OR REPLACE TRIGGER IO_V1
    INSTEAD OF UPDATE ON V1
    FOR EACH ROW
BEGIN
    INSERT INTO t2 ...;
END;

Of course, you can encapsulate all in a procedure and run this procedure rather than UPDATE statement directly.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Do you mean that If I do create the trigger on the view it will trigger when I make the update on t1, and from that trigger I would be able to mutate t1 ? – krise Aug 14 '19 at 08:20
  • 1
    No, the trigger will fire only on update of view v1. If you use this approach then UPDATE privilege on table t1 should be revoked from user. – Wernfried Domscheit Aug 14 '19 at 08:26
  • Ok that does not really sound like what I am looking for. Your other suggestion, encapsulating in a procedure, is it possible to use a procedure like a trigger? Meaning it being called everytime I make an update statement? – krise Aug 14 '19 at 08:29
  • No, if you use a procedure then direct UPDATE on t1 would not be possible, either. – Wernfried Domscheit Aug 14 '19 at 08:43