CODEWITHSUNDEEP
oracle-databasefunctiontriggers
user2108383
user2108383

Reputation: 277

How to write a function for updating audit column in oracle

I'm trying to write a trigger to update couple of columns in a table for that I wrote a function which is doing actual functionality and trigger to execute that function:

But I'm getting the exception can someone guide me what went wrong here:

Function I'm writing :

CREATE OR REPLACE FUNCTION myFunction() 
RETURNS TRIGGER AS $$
BEGIN
     IF OLD.timestamp IS NOT DISTINCT FROM NEW.timestamp THEN
    NEW.lst_updt_ts = now();
     END IF;    
    RETURN NEW;
END;

Trigger:

CREATE OR REPLACE TRIGGER Mytrigger 
BEFORE UPDATE ON mytable
FOR EACH ROW
BEGIN
  myFunction;
END;

When I'm compiling the function

below is the error I'm getting :

PLS-00103: Encountered the symbol ")" when expecting one of the following:

current delete exists prior

Compile error at line 1, column 34

Upvotes: 0

Views: 20

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

The DBMS complains about you using an open parentheses here: myFunction( and then not having any parameters before closing with ). Remove the parentheses.

But then, Oracle doesn't support this trigger function syntax known from PostgreSQL. Neither does it support IS NOT DISTINCT FROM yet.

Unfortunately, you cannot even pass the row like this:

CREATE OR REPLACE FUNCTION myFunction(p_new IN OUT mytable.ROWTYPE%)

because Oracle doesn't treat :new and :old as row records. I've suggested this in Oracle Database Ideas last year. You can vote for it :-) Link: https://community.oracle.com/tech/apps-infra/discussion/comment/16785577#Comment_16785577

There are two ways that I see for now:

  1. Put the code in your trigger directly.
  2. Write a function getting :OLD.timestamp and :NEW.timestamp and :NEW.lst_updt_ts and returning the altered :NEW.lst_updt_ts.

Upvotes: 1

ekochergin
ekochergin

Reputation: 4129

The syntax is definitely not an Oracle. As far as I can understand, you need to update lst_updt_ts to current timestamp if user tries to update it manually. I'd try to write something like that

CREATE OR REPLACE TRIGGER Mytrigger 
BEFORE UPDATE ON mytable
FOR EACH ROW
BEGIN
  IF :OLD.timestamp <> :NEW.timestamp THEN
    :NEW.lst_updt_ts = sysdate; -- or systimestamp;
  END IF;    
END;

Upvotes: 1

Related Questions