October 23rd, 2003, 05:09 AM
I would like to know if an trigger is able to indicate whether an Update/Delete has occured in a table. I know this sounds very ironic but the reason is that I would like to insert records into a history table and indicate with the characters D-for delete and U-for update for the type of modification done for the specific record.
I know it's possible to use 2 different triggers on update/on delete instead of using them together but then I am not sure whether to put D or U into the modification type field of the history table.
If anyone has any other ideas I will gladly appreciate them as to how I will tackle this solution.
October 23rd, 2003, 12:28 PM
make a trigger for ON DELETE on the table for which you care about the modifications, and in the trigger function, INSERT into the modification_type field a 'D'
make another trigger ON UPDATE on the table for which you care about the modifications, and in the trigger function, INSERT into the modificaiton_type field a 'U'.
Now I don't know if you want to record EACH modification or the LAST modification, but if you want EACH then you just put a primary key on that table, select the current MAX primary key, increment it, and then make your compelte INSERT statement based on this primary key. Did I answer your question or not really?
October 24th, 2003, 07:09 AM
Sort of but not really.
Well I understand the solution, but why write it twice when it's going to do exactly the same thing except change the modification indicator to 'U' or 'D' depending on the event. I just thought that although you would say ON UPDATE OR DELETE, the trigger itself would know explicitly that's it's an delete or update is happening hence will be able to send the required argument to the call function.
It would be really cool if this were possible.
October 24th, 2003, 08:05 AM
You can call the same trigger procedure for both. E.g in the top level block of a pl/pgsql trigger function the variable TG_OP will be set with a value of INSERT, UPDATE, or DELETE reflecting on the operation for which the trigger was fired.
October 24th, 2003, 08:19 AM
Firstly, thanx to you guys who have assisted me!
Are there any sites that you can recommend that will assist me as to creation of triggers, also when I create a trigger and call a procedure, I get the error that the function does not exist, do you know why I get such an error.
October 24th, 2003, 09:06 AM
I've had the same sort of problem before, it usually depends on if you create the trigger first or the function first. Somehow you can confuse the backend about which function you're trying to use; I think it assigns an OID to the function when you create it; so if you reference a function from a trigger, it tries to look it up by OID. It may be possible that you need to just drop and then recreate your function. Or, are there any errors when you create the function, or only the trigger?
websites for triggers :
the plpgsql triggers ref. in the postgres documentation
AND the O'Reilly book Practical Postgresql:
stuff about plpgsql and triggers