By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,462 Members | 799 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,462 IT Pros & Developers. It's quick & easy.

How to set TG_ARGV values

P: 2
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.
Jun 22 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 534
This is a pretty dummy example, but it should give you the idea.

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION audit_proc() 
  2. RETURNS trigger AS $$
  3. DECLARE
  4.     arg_num integer;
  5.     arg_color varchar; 
  6. BEGIN
  7.     arg_num   := TG_ARGV[0];
  8.     arg_color := TG_ARGV[1];
  9.  
  10.     IF NEW.id > arg_num THEN
  11.        raise notice 'new record id % is greater than control number %', NEW.id, arg_num;
  12.     ELSE
  13.        raise notice 'new record id % is less than or equal to control number %', NEW.id, arg_num;
  14.     END IF;
  15.  
  16.     IF NEW.color IS NOT NULL AND NEW.color != arg_color THEN
  17.        -- do not allow to insert this value (see RULE and CHECK)
  18.        raise exception 'new color % is not equal to control color %', NEW.color, arg_color;
  19.     END IF;
  20.  
  21.     -- everything is OK, let's set the time and the author of this update
  22.     NEW.update_date := current_timestamp;
  23.     NEW.update_user := current_user;
  24.     return NEW;
  25. END;
  26. $$
  27. LANGUAGE plpgsql;
  28.  
  29. -----------------------------------
  30.  
  31. CREATE TRIGGER trig_audit
  32.    BEFORE INSERT OR UPDATE ON bar
  33.    FOR EACH ROW
  34.    EXECUTE PROCEDURE audit_proc(100, 'blue');
  35.  
  36.  
Jun 23 '07 #2

P: 2
*** 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" ?
Jun 24 '07 #3

Expert 100+
P: 534
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
Jun 24 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.