Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trigger problem

Trigger problem

From
"kasper"
Date:
Hi guys

Im tryint to make a trigger that marks a tuble as changed whenever someone
has updated it

my table looks something like this

create table myTable (   ...   changed boolean;
)

now ive been working on a trigger and a sp that looks like this, but it
doesnt work...

create function myFunction returns trigger as '   begin       new.changed = true;       return new;   end;
' language 'plpgsql';

create trigger myTrigger
after update on lektioner
for each row
execute procedure myFunction();


the code compiles, runs, and doesnt whine about anything, but nothing
changes...

any ideas??

- Kasper

Re: Trigger problem

From
Mike Rylander
Date:
kasper wrote:

> Hi guys
> 
> Im tryint to make a trigger that marks a tuble as changed whenever someone
> has updated it
> 
> my table looks something like this
> 
> create table myTable (
>     ...
>     changed boolean;
> )
> 
> now ive been working on a trigger and a sp that looks like this, but it
> doesnt work...
> 
> create function myFunction returns trigger as '
>     begin
>         new.changed = true;

The line above is using the SQL equaliy opperator, you want the assignment
operator:
 :=

as in 
       new.changed := true;

>         return new;
>     end;
> ' language 'plpgsql';
> 
> create trigger myTrigger
> after update on lektioner
> for each row
> execute procedure myFunction();
> 
> 
> the code compiles, runs, and doesnt whine about anything, but nothing
> changes...
> 
> any ideas??
> 
> - Kasper

-miker

Re: Trigger problem

From
Stephan Szabo
Date:
On Tue, 8 Jun 2004, kasper wrote:

> Im tryint to make a trigger that marks a tuble as changed whenever someone
> has updated it
>
> my table looks something like this
>
> create table myTable (
>     ...
>     changed boolean;
> )
>
> now ive been working on a trigger and a sp that looks like this, but it
> doesnt work...
>
> create function myFunction returns trigger as '
>     begin
>         new.changed = true;
>         return new;
>     end;
> ' language 'plpgsql';
>
> create trigger myTrigger
> after update on lektioner
> for each row
> execute procedure myFunction();

You want a before update trigger if you want to update the new row like
that.

Re: Trigger problem

From
Jan Wieck
Date:
On 6/8/2004 2:57 PM, Mike Rylander wrote:

> kasper wrote:
> 
>> Hi guys
>> 
>> Im tryint to make a trigger that marks a tuble as changed whenever someone
>> has updated it
>> 
>> my table looks something like this
>> 
>> create table myTable (
>>     ...
>>     changed boolean;
>> )
>> 
>> now ive been working on a trigger and a sp that looks like this, but it
>> doesnt work...
>> 
>> create function myFunction returns trigger as '
>>     begin
>>         new.changed = true;
> 
> The line above is using the SQL equaliy opperator, you want the assignment
> operator:
> 
>   :=
> 
> as in 
> 
>         new.changed := true;

PL/pgSQL accepts both. What's wrong is that it's an AFTER trigger, which 
is fired AFTER the new row is already stored on disk and thus cannot 
change it any more.


Jan

> 
>>         return new;
>>     end;
>> ' language 'plpgsql';
>> 
>> create trigger myTrigger
>> after update on lektioner
>> for each row
>> execute procedure myFunction();
>> 
>> 
>> the code compiles, runs, and doesnt whine about anything, but nothing
>> changes...
>> 
>> any ideas??
>> 
>> - Kasper
> 
> -miker
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #