Syntax for a trigger that fires on update based on the value of a certain row

Hello,

I have a table (TableA) with a column called 'status'.

What is the syntax to create a trigger whenever the column 'status' is updated to the value 'CLOSED'?

Thank you!

rss2


rss2Asked:
Who is Participating?
 
answer_meConnect With a Mentor Commented:
try this:

Create trigger trg_TableAUpdate
on tablea for update 
as
begin
	if( update(status))
	begin
		if exists( Select top 1 1 from tablea join deleted  on tablea.<id> = deleted.<id> and tablea.status='closed')
		begin
		end
	end
end

Open in new window

0
 
answer_meCommented:
this code will work on sql server
0
 
ivanovnCommented:
For the code that would work on PostgreSQL you need to do the following:
1. Create a trigger function in whatever procedural language you want For example, attached is a plpgsql function. This function will check the value the field is being updated to.
2. Then you create a trigger that is called on each row. The trigger function will take care of checking if the value was set to 'CLOSED'.
CREATE OR REPLACE FUNCTION test_trig_f() RETURNS trigger AS
BEGIN
	IF NEW.status='CLOSED' THEN
		//do whatever processing you wanted here
	END IF;
END;
LANGUAGE 'plpgsql' VOLATILE;
 
CREATE TRIGGER test_trig AFTER UPDATE ON test_table FOR EACH ROW
   EXECUTE PROCEDURE test_trig_f();

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.