Using a trigger to cancel an UPDATE, INSET or DELETE

[pandpp]

Hello there. I'm not a trigger expert, but happy to give most anything a try.

I have a 3rd party app, part of which I need to lock down from being able to edit data in the DB.
As such, I decided (maybe stupidly) that it was easy enough to create a trigger to catch the change & put a stop to it.

I couldn't find a way to stop the INSERT, UPDATE or DELETE, so decided to first, concentrate on just the INSERT & DELETE, setting the :new values back to the ld values & letting it go through like that.

The Oracle error I get for this is
[tt]ORA-04084: cannot change NEW values for this trigger type[/tt]
which seems to go against the doco I read.

Then I decided to let the INSERT/DELETE go through, then put the record back, from the ld values.

This gives me an Oracle error of
[tt]ORA-04091: table ELLIPSE.MSF071 is mutating, trigger/function may not see it[/tt]
which, in theory is hardly surprising, since it's become a recursive problem.

At this point I tried turning the Trigger off, from within the Trigger, I figured I had nothing left to lose.
As expected, that wouldn't even compile...


My first question is, is what I'm trying to do achievable, can I stop an INSERT/UPDATE/DELETE from occurring in a table at all?
My second question (hoping like mad the last answer was positive) is, HOW???

Thanks for any suggestions, regardless of stupidity.
It could be nothing more simple than I need to break it up into several single Triggers, to avoid the recursiveness issue, I don't know. Hopefully somebody does.

Thanks,
Peter.

CREATE OR REPLACE TRIGGER VOTS_data
AFTER INSERT OR UPDATE OR DELETE
    ON MSF071
    FOR EACH ROW

BEGIN
    IF :new.Entity_Type = 'EQP' AND :new.Ref_No BETWEEN 200 AND 203 AND :new.Seq_Num = '001' OR
            :old.Entity_Type = 'EQP' AND :old.Ref_No BETWEEN 200 AND 203 AND :old.Seq_Num = '001' THEN
--         ALTER TRIGGER VOTS_data DISABLE;
        
        IF INSERTING THEN
            DELETE
            FROM
                MSF071
            WHERE
                Entity_Type = :new.Entity_Type
                AND Entity_Value = :new.Entity_Value
                AND Ref_No = :new.Ref_No
                AND Seq_Num = :new.Seq_Num
                AND Ref_Code = :new.Ref_Code;
        ELSIF DELETING THEN
            INSERT INTO
                MSF071
                    (
                        Entity_Type,
                        Entity_Value,
                        Ref_No,
                        Seq_Num,
                        Ref_Code,
                        Std_Txt_Key,
                        Last_Mod_Date,
                        Last_Mod_Time,
                        Last_Mod_User
                    )
                VALUES
                    (
                        :old.Entity_Type,
                        :old.Entity_Value,
                        :old.Ref_No,
                        :old.Seq_Num,
                        :old.Ref_Code,
                        :old.Std_Txt_Key,
                        :old.Last_Mod_Date,
                        :old.Last_Mod_Time,
                        :old.Last_Mod_User
                    );
        END IF;

--         ALTER TRIGGER VOTS_data ENABLE;
    END IF;
END;

 

[Thargy]

pandpp,

yes, you can use triggers to achieve what you want, but it would be the very last method I would attempt.

If you want the application to be able to read the table and nothing more, then just change its permissions on the table.

REVOKE INSERT, UPDATE, DELETE ON MSF071 FROM <3RD PARTY LOGIN ID>

should do the trick.

Regards

Tharg

Grinding away at things Oracular

 

[pandpp]

thargtheslayer,

Thanks for the suggestion.

Unfortunately, I probably didn't fully explain the problem. The 3rd party app is fully allowed to have write access to the table.

What I need is to be able to make certain records within that table effectively read only, depending upon certain fields within that record.

As such, rather than modifying the app on a per record basis (expensive), I decided to leave the app as it stood & ensure the change at the DB wasn't able to be made (due to criteria), hence the trigger idea.

Peter.

 

[Beilstwh]

CREATE OR REPLACE TRIGGER VOTS_data
AFTER INSERT OR UPDATE OR DELETE
ON MSF071
FOR EACH ROW

BEGIN
IF :new.Entity_Type = 'EQP' AND :new.Ref_No BETWEEN 200 AND 203 AND :new.Seq_Num = '001' OR
ld.Entity_Type = 'EQP' AND ld.Ref_No BETWEEN 200 AND 203 AND ld.Seq_Num = '001' THEN
RAISE_APPLICATION_ERROR(-20101,'YOU ARE NOT ALLOWED TO MODIFY THE TABLE');
END IF;
END;

Bill
Oracle DBA/Developer
New York State, USA