Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I copy :OLD and :NEW pseudo-records in/to an Oracle stored procedure?

I have an AFTER INSERT OR UPDATE OR DELETE trigger that I'm writing to store every record revision that occurs in a certain table, by copying the INSERT and UPDATE :NEW values into a mirror table, and for DELETE the :OLD values.

I could un-clutter my code considerably by conditionally passing either the :NEW or :OLD record into a procedure which would then do the insert into my history table. Unfortunately I cannot seem to find a way to pass the entire :OLD or :NEW record.

Am I missing something or is there no way to avoid enumerating every :NEW and :OLD column as I invoke my insert procedure?

I want to do the following:

DECLARE
  PROCEDURE LOCAL_INSERT(historyRecord in ACCT.ACCOUNTS%ROWTYPE) IS
  BEGIN
    INSERT INTO ACCT.ACCOUNTS_HISTORY (ID, NAME, DESCRIPTION, DATE) VALUES (historyRecord.ID, historyRecord.NAME, historyRecord.DESCRIPTION, SYSDATE);
  END;
BEGIN
  IF INSERTING OR UPDATING THEN
    LOCAL_INSERT(:NEW);
  ELSE --DELETING
    LOCAL_INSERT(:OLD);
  END IF;
END;

But I'm stuck doing this:

DECLARE
  PROCEDURE LOCAL_INSERT(id in ACCT.ACCOUNTS.ID%TYPE,
                         name in ACCT.ACCOUNTS.NAME%TYPE,
                         description in ACCT.ACCOUNTS.DESCRIPTION%TYPE) IS
  BEGIN
    INSERT INTO ACCT.ACCOUNTS_HISTORY (ID, NAME, DESCRIPTION, DATE) VALUES (id, name, description, SYSDATE);
  END;
BEGIN
  IF INSERTING OR UPDATING THEN
    LOCAL_INSERT(:NEW.ID, :NEW.NAME, :NEW.DESCRIPTION);
  ELSE --DELETING
    LOCAL_INSERT(:OLD.ID, :OLD.NAME, :OLD.DESCRIPTION);
  END IF;
END;

Okay, so it doesn't look like a big difference, but this is just an example with 3 columns rather than dozens.

like image 280
aw crud Avatar asked Feb 25 '10 20:02

aw crud


People also ask

Which is better trigger or stored procedure?

Stored procedures can be invoked explicitly by the user. It's like a java program , it can take some input as a parameter then can do some processing and can return values. On the other hand, trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete).

Are triggers and stored procedures the same?

Triggers are similar to stored procedures but differ in the way that they are invoked. Support for triggers in MySQL is only included beginning with release 5.0. 2. A trigger can only be associated with a table and defined to fire when an INSERT, DELETE or UPDATE statement is performed on the table.

Can a stored procedure be called from a trigger?

MySQL allows you to call a stored procedure from a trigger by using the CALL statement. By doing this, you can reuse the same stored procedure in several triggers. However, the trigger cannot call a stored procedure that has OUT or INOUT parameters or a stored procedure that uses dynamic SQL.


2 Answers

It isn't. You have to do it yourself through enumeration.

The reasons it can't/doesn't work automatically include:

  • the :old and :new are default conventions; you can name the :old and :new references to be whatever you want through the REFERENCING clause of the CREATE TRIGGER statement.

  • you'd have to have a public declaration of a type (through CREATE TYPE or through a package declaration) to be able to use it as an argument to another piece of code.

  • trigger code is interpreted code, not compiled code.

like image 100
Adam Musch Avatar answered Sep 28 '22 08:09

Adam Musch


I don't think it's possible like that. Documentation doesn't mention anything like that.

This would certainly cost performance, but you could try to define your trigger AFTER INSERT and another one BEFORE UPDATE OR DELETE, and in the trigger do something like:

SELECT *
INTO rowtype_variable
FROM accounts
WHERE accounts.id = :NEW.id; -- :OLD.id for UPDATE and DELETE

and then call your procedure with that rowtype_variable.

like image 38
Peter Lang Avatar answered Sep 28 '22 08:09

Peter Lang