Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

totti24

Programmer
Aug 2, 2002
32
MY
hi there. it's me again. i'm encountering some problems to determine the insert, update and delete process in a function.

currently i'm writing a function to update a column in a table called myflag. If the process is an INSERT then myflag will be udpated with 'I'. If the process is update, then myflag will be updated with 'U' and 'D' is for delete.

i have tried using the syntax below to determine it, but it doesn't seems to work.

if TG_OP = ''INSERT'' then
update myflag = 'I'
elsif TG_OP = ''UPDATE'' then
update myflag = 'U'
end if;

the problem i'm facing here is that, insert is actually update and update is actually insert. therefore the statement above is not working. can i know is there any other way that i cann use to determine the process whether it is an update, insert 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("DBI:pg: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("DBI:pg: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...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top