Thread: Help with trigger

Help with trigger

From
"Pradeepkumar, Pyatalo (IE10)"
Date:

> 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
>