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