rss2
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Answer_Me
this code will work on sql server
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'.
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();