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.

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