Solved

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

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.

770 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