Re: Help with trigger - Mailing list pgsql-novice

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/