Solved

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

Posted on 2007-12-05
3
198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

728 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