how to determine whether process is insert, update or delete?

Hi totti24,

Why not use a timestamp field? The timestamp would indicate when the record was last modified, either by insert or update. This would give you more information than a simple ture or false.

LelandJ Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox

in oracle there are 2 boolean variables, inserting and deleting
the update comes under the else flag

This can be found in the triggers defined in a table
Just check if postgresql has something similar

hi leland123,

if i use the timestamp field, how do i know whether the row is an update or insert? do i still need a flag to determine it? bcoz in my tables, there will be 3 extra fields, which is audit_user, audit_date and the flag. hope u can advice me more on this. thx.

Hi totti24,

I'm not seeing the importance of whether a write to the postgres table is an insert or an update. If this useful to you, it can easily be done with any front-end application. Have a field in your table to flag whether the item is an insert or update. Then when inserting a new record place the value 1 for insert or 0 for update like:

********* Using Perl *****************************

######If in the adding new records program you would do inserts

$Lfirstname="Leland";
$Llastname="Jackson";
$Laddress="317 Lexington Ave.";
$Lcity="Abilene";
$Lstate="TX";
$Lsip="79605";



$dbh = DBI->connect("DBIg:dbname=$the_db;host=$the_host", $the_user, $the_pass_wd, {'RaiseError' => 1});

$dbh->do("INSERT INTO mytable (firstname,lastname,address,city,state,zip,the_flag,the_time) VALUE ($Lfirstname,$Lastname,$Laddress,$Lcity,$Lstate,$Lzip,'1','now');

#####if in the edit records program you would do updates

$LKey_No='1188443';
$Lfirstname="Leland";
$Llastname="Jackson";
$Laddress="317 Lexington Ave.";
$Lcity="Abilene";
$Lstate="TX";
$Lsip="79605";

$dbh = DBI->connect("DBIg:dbname=$the_db;host=$the_host", $the_user, $the_pass_wd, {'RaiseError' => 1});

$dbh->do("UPDATE mytable SET firstname=$Lfirstname,lastname=$Llastname,address=$Laddress,city=$Lcity,state=$Lstate,zip=$Lzip,the_flag='0',the_time='now' WHERE Key_NO=$LKey_NO"


LelandJ Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox

if you are still interested,
this can be done by checking the tg_op variable

if tg_op = ''insert'' then
--write some code
end if
if tg_op = ''update'' then
and so on

totti
sorry about the last post
I was dead tired last night when I wrote that

I take it from reading between the lines that this function you are creating is not a trigger, but simply called from somewhere else to update a certain table. So, do that, and define separate triggers to handle the timestamp column:

1. Create a function that takes a simple argument, which can be one of 3 test values: "insert", "update", "delete". This function will do nothing but update the timestamp column for the current record, based on the argument.

2. Define 3 separate triggers, for insert, update, and delete on your table. From the trigger documentation ( you will see that a trigger can have optional additional arguments. So, for each trigger, just include the argument 'insert', 'update', 'delete' as appropriate (of course, calling the function in part 1)

3. Or, if you are ready to take on another cool concept, just create a rule to handle both parts 1 and 2 above:
-------------------------------------------

My PostgreSQL FAQ --

hi guys, thanks for all the replies. i managed to work the thing out by using TG_OP var. i manage to detect whether the operation is an insert, update or delete by using TG_OP var. now the thing is working perfectly =). probably i made some mistakes early on. cheers...