Help with trigger - Mailing list pgsql-novice

From Pradeepkumar, Pyatalo (IE10)
Subject Help with trigger
Date
Msg-id 77ED2BF75D59D1439F90412CC5B1097412C45641@ie10-sahara.hiso.honeywell.com
Whole thread Raw
List pgsql-novice

> Hi all,
>
> I have a table something like this....
> table Alarm(
>         AlarmId integer,
>         AlarmName varchar,
>         Sentflag smallint,
>         AckFlag smallint,
>         RTNFlag smallint,
>         AutoRTNFlag smallint,
>         cookie long);
>
> I am trying to write  a trigger on this table for insert and update
> operations.
> In the above table cookie field is not unique....there can be a max of 2
> tuples with a given cookie number.
> Now in the trigger function i check if there are more than one tuple with
> the cookie number of the tuple being modified or inserted into the table.
> If there are 2 tuples with the same cookie, i need to check if
> SentFlag,AckFlag,RTNFlag of both the tables are equal to 1...if so delete
> both the tuples from the table.
> I am not able to refer to the tuples in the function....how can i refer to
> the fields of both the tuples.
>
> The trigger function is something like this
>
> CREATE FUNCTION PP_DeleteAlarm() RETURNS TRIGGER AS '
> DECLARE
>     number INTEGER = 0;
> BEGIN
>     --check if the previous operation on the table is UPDATE
>     IF TG_OP = ''UPDATE''  OR TG_OP = ''INSERT'' THEN
>
>         SELECT INTO number COUNT(*) FROM Alarm WHERE Cookie =
> NEW.Cookie;
>
>         IF number > 1 THEN
>
>         --check for the 3 flags of both the tuples -- how ???
>
>         --check if all the three flags in the Alarm table are 0
>         IF NEW.Sent = 1 AND NEW.Ack = 1 AND NEW.RTN = 1 THEN
>
>             --Delete the tuple from the table
>             DELETE FROM Alarm
>             WHERE PointNum = NEW.PointNum;
>
>         END IF;
>
>     END IF;
>
>     RETURN OLD;
>
> END ;
> ' LANGUAGE 'plpgsql';
>
>
>
> With Best Regards,
> Pradeep Kumar P.J
>

pgsql-novice by date:

Previous
From: John Browne
Date:
Subject: Conditional Relationships?
Next
From: Ron St-Pierre
Date:
Subject: Re: Conditional Relationships?