I would like to programmatically create audit trail functions which are called by triggers for custom user tables. This will be used for audit trail generation in our application. The user is able to define a custom table. Therefore, I need a custom audit trail function. The audit trail function outputs the row changes in human readable form (e.g., "process name changed from "my process" to "your process" by user xxxx on host xxx".
The basic steps are as follows:
1. User defines the table
2. Create table via EXEC SQL EXECUTE IMMEDIATE :my_sql_cmd
3. Programatically constructs the audit trail function for insert/update/delete.
4. Create the function via EXEC SQL EXECUTE IMMEDIATE :my_sql_cmd
5. Create the trigger via EXEC SQL EXECUTE IMMEDIATE :my_sql_cmd
Here is an example in function source which is passed to "EXEC SQL EXECUTE IMMEDIATE" via char*:
2
3
4
5
6
7
8
9
10
11
12
| CREATE OR REPLACE FUNCTION name_changes_log()
RETURNS TRIGGER AS
$BODY$
BEGIN
IF NEW.name <> OLD.name THEN
INSERT INTO kids_audit(kids_id,kids_name,modified_on)
VALUES(OLD.id,OLD.name,now());
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql; |
The "execute immediate" succeeds, but the function is not created. The application is connected to the database as a user which has permission to create functions.
I could output the function text to a file and then use "psql" to process the "create trigger", but this seems a bit hacky.
Thanks in advance,
Thomas