pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedColumn Specific Update Trigger Routine
5+ messages / 3 participants
[nested] [flat]
* Column Specific Update Trigger Routine
@ 2010-05-06 20:12 ` Plugge, Joe R. <JRPlugge@west.com>
2010-05-06 20:51 ` Re: Column Specific Update Trigger Routine Plugge, Joe R. <JRPlugge@west.com>
1 sibling, 1 reply; 5+ messages in thread
From: Plugge, Joe R. @ 2010-05-06 20:12 UTC (permalink / raw)
To: pgsql-sql
I am trying to create a update trigger on a table that basically will only fire when a specific column is updated. I am using version 8.4.3.
My plan of attack was to always fire on any row update, and pass in the OLD and NEW column that I want to check.
CREATE TRIGGER check_lockout
AFTER UPDATE ON acct_table
FOR EACH ROW
EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);
This fails with :
[postgres@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb
ERROR: syntax error at or near "OLD"
LINE 4: EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);
What am I doing wrong? Or is there a better way to go about this?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Column Specific Update Trigger Routine
2010-05-06 20:12 ` Column Specific Update Trigger Routine Plugge, Joe R. <JRPlugge@west.com>
@ 2010-05-06 20:51 ` Plugge, Joe R. <JRPlugge@west.com>
0 siblings, 0 replies; 5+ messages in thread
From: Plugge, Joe R. @ 2010-05-06 20:51 UTC (permalink / raw)
To: pgsql-sql
Nevermind all, I figured it out ....
Thanks Dmitriy ...
From: Dmitriy Igrishin [mailto:dmitigr@gmail.com]
Sent: Thursday, May 06, 2010 3:25 PM
To: Plugge, Joe R.
Subject: Re: [SQL] Column Specific Update Trigger Routine
Hey Plugge,
You dont need to pass OLD.* or NEW.* to the trigger function.
These structures available from within trigger function.
Note, that in PostgreSQL 9 you will able to create trigger
which call function only when some column of the table
affected.
Regards,
Dmitriy
2010/5/7 Plugge, Joe R. <JRPlugge@west.com<mailto:JRPlugge@west.com>>
I am trying to create a update trigger on a table that basically will only fire when a specific column is updated. I am using version 8.4.3.
My plan of attack was to always fire on any row update, and pass in the OLD and NEW column that I want to check.
CREATE TRIGGER check_lockout
AFTER UPDATE ON acct_table
FOR EACH ROW
EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);
This fails with :
[postgres@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb
ERROR: syntax error at or near "OLD"
LINE 4: EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);
What am I doing wrong? Or is there a better way to go about this?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Column Specific Update Trigger Routine
@ 2010-05-06 20:59 ` Justin Graf <justin@magwerks.com>
2010-05-06 21:02 ` Re: Column Specific Update Trigger Routine Plugge, Joe R. <JRPlugge@west.com>
1 sibling, 1 reply; 5+ messages in thread
From: Justin Graf @ 2010-05-06 20:59 UTC (permalink / raw)
To: Plugge, Joe R. <JRPlugge@west.com>; pgsql-sql
On 5/6/2010 4:12 PM, Plugge, Joe R. wrote:
>
> I am trying to create a update trigger on a table that basically will
> only fire when a specific column is updated. I am using version 8.4.3.
>
> My plan of attack was to always fire on any row update, and pass in
> the OLD and NEW column that I want to check.
>
> CREATE TRIGGER check_lockout
>
> AFTER UPDATE ON acct_table
>
> FOR EACH ROW
>
> EXECUTE PROCEDURE
> acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);
>
> This fails with :
>
> [postgres@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb
>
> ERROR: syntax error at or near "OLD"
>
> LINE 4: EXECUTE PROCEDURE
> acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);
>
> What am I doing wrong? Or is there a better way to go about this?
>
You don't call the trigger procedure with the old and new as parameters
new and old are automatically created for the function acct_unlock()
CREATE TRIGGER check_lockout
AFTER UPDATE ON acct_table
FOR EACH ROW
EXECUTE PROCEDURE acct_unlock();
Next the trigger function would look something like this
create or replace function acct_unlock()
returns trigger as
$$
if (OLD.userid <> NEW.password)
do something
end if;
$$
keep in mind the acct_unlock must be returns trigger
Then return either NEW or the OLD record
OLD if not changing the record or NEW if the updated values are to be
stored in the table.
All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Column Specific Update Trigger Routine
2010-05-06 20:59 ` Re: Column Specific Update Trigger Routine Justin Graf <justin@magwerks.com>
@ 2010-05-06 21:02 ` Plugge, Joe R. <JRPlugge@west.com>
2010-05-06 21:13 ` Re: Column Specific Update Trigger Routine Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Plugge, Joe R. @ 2010-05-06 21:02 UTC (permalink / raw)
To: Justin Graf <justin@magwerks.com>; pgsql-sql
This is what I have and it seems to work:
CREATE OR REPLACE FUNCTION holly_unlock() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF OLD.password != NEW.password
THEN
UPDATE hms_mtusers_rw set loginfailedcount = 0 WHERE userid = OLD.userid and ownerid = OLD.ownerid;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$;
Thanks !!
From: Justin Graf [mailto:justin@magwerks.com]
Sent: Thursday, May 06, 2010 3:59 PM
To: Plugge, Joe R.; pgsql-sql@postgresql.org
Subject: Re: [SQL] Column Specific Update Trigger Routine
On 5/6/2010 4:12 PM, Plugge, Joe R. wrote:
I am trying to create a update trigger on a table that basically will only fire when a specific column is updated. I am using version 8.4.3.
My plan of attack was to always fire on any row update, and pass in the OLD and NEW column that I want to check.
CREATE TRIGGER check_lockout
AFTER UPDATE ON acct_table
FOR EACH ROW
EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);
This fails with :
[postgres@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb
ERROR: syntax error at or near "OLD"
LINE 4: EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);
What am I doing wrong? Or is there a better way to go about this?
You don't call the trigger procedure with the old and new as parameters
new and old are automatically created for the function acct_unlock()
CREATE TRIGGER check_lockout
AFTER UPDATE ON acct_table
FOR EACH ROW
EXECUTE PROCEDURE acct_unlock();
Next the trigger function would look something like this
create or replace function acct_unlock()
returns trigger as
$$
if (OLD.userid <> NEW.password)
do something
end if;
$$
keep in mind the acct_unlock must be returns trigger
Then return either NEW or the OLD record
OLD if not changing the record or NEW if the updated values are to be stored in the table.
All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Column Specific Update Trigger Routine
2010-05-06 20:59 ` Re: Column Specific Update Trigger Routine Justin Graf <justin@magwerks.com>
2010-05-06 21:02 ` Re: Column Specific Update Trigger Routine Plugge, Joe R. <JRPlugge@west.com>
@ 2010-05-06 21:13 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2010-05-06 21:13 UTC (permalink / raw)
To: Plugge, Joe R. <JRPlugge@west.com>; +Cc: Justin Graf <justin@magwerks.com>; pgsql-sql
"Plugge, Joe R." <JRPlugge@west.com> writes:
> This is what I have and it seems to work:
> IF OLD.password != NEW.password
It'd be better to write "IF OLD.password IS DISTINCT FROM NEW.password".
The way with != will not do what you want if one value is null and the
other isn't. It's possible this doesn't matter in this particular case
(if password can't ever be null in this table), but being careful about
nulls is a good habit to cultivate.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-05-06 21:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-06 20:12 ` Plugge, Joe R. <JRPlugge@west.com>
2010-05-06 20:51 ` Plugge, Joe R. <JRPlugge@west.com>
2010-05-06 20:59 ` Justin Graf <justin@magwerks.com>
2010-05-06 21:02 ` Plugge, Joe R. <JRPlugge@west.com>
2010-05-06 21:13 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox