Thread: Help with trigger

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

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/

On Fri, Oct 08, 2004 at 12:52:52AM -0600, Michael Fuhr wrote:

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

I should also point out that the trigger function I posted doesn't
deal with concurrency.  For example, if an update happens in one
transaction, and an insert happens in another transaction before
the update commits, then you could end up with two records that
should have been deleted but weren't.  The function I posted was
merely to show how one might perform the tests you need to make.

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

Dear Group,
 I have a table that stores the description of an
experiment, date and person details who performed the
experiment.
In the last column I want to give the directory where
the files from these experiments are stored.

My questions:

1. Once a user queries this table from a command-line
(using sql commands). How can he get the data to
download?

I want my user to be able to download that data.

Is something possible from
SELECT statements or do I have to do something. Can
any one help me with should I do.

Thank you.

Kumar.




__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail