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

logically-delete a row rather than actually remove in from table in postgreSQL

P: 2
I want to write a trigger so that rows deleted from the table are logically-deleted, rather than being actually removed. That is, such rows are marked as being deleted, rather than actually being deleted from the table.

in sql server I know that we can use INSTEAD OF DELETE trigger but I don`t know how can I do the same action in PostgreSQL.

It needed to write a function that update the rows which asked for delete.

please help me with the trigger and related function which is needed .
Sep 13 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 700
I want to write a trigger so that rows deleted from the table are logically-deleted, rather than being actually removed. That is, such rows are marked as being deleted, rather than actually being deleted from the table.

in sql server I know that we can use INSTEAD OF DELETE trigger but I don`t know how can I do the same action in PostgreSQL.

It needed to write a function that update the rows which asked for delete.

please help me with the trigger and related function which is needed .
In postgres u have rules (in which u can use instead) and u can use triggers 'after' which do some updates and return null.
Give more informations about u'r problem.
Sep 13 '07 #2

P: 2
In postgres u have rules (in which u can use instead) and u can use triggers 'after' which do some updates and return null.
Give more informations about u'r problem.
Thanks for your help .
I want to write a trigger that acts like this:
When a user executes a DELETE statement, the trigger must set the WhenDeleted value to the systemís CURRENT_TIMESTAMP value, for each of the rows targeted by that DELETE statement.
Sep 15 '07 #3

Expert 100+
P: 700
Thanks for your help .
I want to write a trigger that acts like this:
When a user executes a DELETE statement, the trigger must set the WhenDeleted value to the systemís CURRENT_TIMESTAMP value, for each of the rows targeted by that DELETE statement.

i don't see the trigger, so i write it

CREATE FUNCTION LogicalDeleteFunction ()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
declare
t timestamp;

BEGIN
t=current_timestamp;
UPDATE patients
SET
patients.whendeleted = current_timestamp
JOIN deleted ON deleted.patientnum = patients.patientnum
??no where clause
what is deleted table??


return OLD;
END;
$$;

create trigger my_trig before delete on patients for each row execute procedure LogicalDeleteFunction ();
Sep 15 '07 #4

Post your reply

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