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
