Solved

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

Posted on 2007-12-05
3
193 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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

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