Re: Help with trigger

On Wed, Oct 06, 2004 at 10:26:00PM -0700, Pradeepkumar, Pyatalo (IE10) wrote:
>
> I have a table something like this....
> table Alarm(
> AlarmId integer,
> AlarmName varchar,
> Sentflag smallint,
> AckFlag smallint,
> RTNFlag smallint,
> AutoRTNFlag smallint,
> cookie long);

PostgreSQL doesn't have a LONG type -- perhaps you mean BIGINT.

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

Does the application guarantee the 2-tuple limit or does the database
need to enforce it? If the latter, then what should happen if more
than 2 tuples are inserted?

> 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 below, fired after inserts and updates, might
be close to what you need. However, it doesn't enforce the 2-tuple
limit -- it only contains the logic to delete records based on the
criteria you specified. It worked in the minimal tests I performed,
but I'd recommend doing more thorough testing before using it in
production.

If this isn't what you're looking for, then please clarify your
requirements.

CREATE OR REPLACE FUNCTION PP_DeleteAlarm() RETURNS TRIGGER AS '
DECLARE
row RECORD;
BEGIN
-- Does this record meet the criteria for deletion?
IF NEW.SentFlag = 1 AND NEW.AckFlag = 1 AND NEW.RTNFlag = 1 THEN

-- Look for another record for this cookie that also meets
-- the criteria for deletion.
SELECT INTO row AlarmId
FROM Alarm
WHERE cookie = NEW.cookie
AND AlarmId <> NEW.AlarmId
AND SentFlag = 1
AND AckFlag = 1
AND RTNFlag = 1;

-- If we found another record then delete them both.
IF FOUND THEN
DELETE FROM Alarm WHERE AlarmId = NEW.AlarmId OR AlarmId = row.AlarmId;
-- or perhaps WHERE cookie = NEW.cookie
END IF;
END IF;

RETURN NULL;
END;
' LANGUAGE plpgsql;

DROP TRIGGER alarm_after ON Alarm;

CREATE TRIGGER alarm_after AFTER INSERT OR UPDATE ON Alarm
FOR EACH ROW EXECUTE PROCEDURE PP_DeleteAlarm();

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/