Solved

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

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now