?
Solved

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

Posted on 2007-12-05
3
Medium Priority
?
209 Views
Last Modified: 2010-03-20
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


0
Comment
Question by:rss2
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
answer_me earned 2000 total points
ID: 20411016
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
 
LVL 10

Expert Comment

by:answer_me
ID: 20411030
this code will work on sql server
0
 
LVL 10

Expert Comment

by:ivanovn
ID: 20415064
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question