Help with trigger

#1

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Help with trigger

comp.databases.postgresql.novice comp.databases.postgresql.novice

Help with trigger - 10-07-2004 , 03:36 AM