How to set TG_ARGV values

Jorge Destri Jr

Hi,

I need to create a table for audit. The data will come from of the triggers.
Some values are variables and need to be passing as parameters.
How to set TG_ARGV to hold that values?

Thanks.

michaelb

This is a pretty dummy example, but it should give you the idea.

Expand|Select|Wrap|Line Numbers

 
CREATE OR REPLACE FUNCTION audit_proc() 

RETURNS trigger AS $$

DECLARE

    arg_num integer;

    arg_color varchar; 

BEGIN

    arg_num   := TG_ARGV[0];

    arg_color := TG_ARGV[1];
 
    IF NEW.id > arg_num THEN

       raise notice 'new record id % is greater than control number %', NEW.id, arg_num;

    ELSE

       raise notice 'new record id % is less than or equal to control number %', NEW.id, arg_num;

    END IF;
 
    IF NEW.color IS NOT NULL AND NEW.color != arg_color THEN

       -- do not allow to insert this value (see RULE and CHECK)

       raise exception 'new color % is not equal to control color %', NEW.color, arg_color;

    END IF;
 
    -- everything is OK, let's set the time and the author of this update

    NEW.update_date := current_timestamp;

    NEW.update_user := current_user;

    return NEW;

END;

$$

LANGUAGE plpgsql;
 
-----------------------------------
 
CREATE TRIGGER trig_audit

   BEFORE INSERT OR UPDATE ON bar

   FOR EACH ROW

   EXECUTE PROCEDURE audit_proc(100, 'blue');

Jorge Destri Jr

*** EXECUTE PROCEDURE audit_proc(100, 'blue');

If I have a Dellphi program that fired this trigger, how to change "(100, "blue")" for variables like "application user" and "current IP address" ?

michaelb

The trigger is fired in response to the specified action, such as INSERT, UPDATE or DELETE.
The trigger procedure (function) cannot be explicitly passed any arguments, instead it has access to NEW, OLD, TG_ARGV[] and few other special variables.
You can set the TG_ARGV[] values in the trigger that invokes the function.

I think it would help you if you review the man pages following these links.
Let us know if you have question afterwards.

Create Trigger
Trigger Procedures

By the way, you may also be interested in looking into the RULES
This is a pretty powerful facility; in two words you can say this:
Instead of update (or insert, or delete) do the following:
- check on some values, conditions, etc
- set some values if necessary
- approve or reject the update

How to set TG_ARGV values

re: How to set TG_ARGV values

re: How to set TG_ARGV values

re: How to set TG_ARGV values

Similar PostgreSQL Database bytes

/bytes/development

/bytes/it

/bytes/about