Re: How do you use a trigger in Oracle to delete an unwanted insert?

The most simple solution would be just raise an application error. This will rollback automatically. However, if your app is committing multiple inserts at the same time, they will rollback too. The other solution is create a package, with an pl/sql table of rowids. This should be cleaned before insert or update on statement level. It should store the rowid on the for each row trigger and loop through the table and delete after insert or update on statement level.

Hth,

--
Sybrand Bakker, Oracle DBA
<jsenarat_at_my-deja.com> wrote in message news:7s95g1$6eq$1_at_nnrp1.deja.com...
> How do you use a trigger in Oracle to delete an unwanted insert?
>
> I'm trying not to let an application insert nulls to a table
> column/row, and would like to stop this via a trigger, without
> generating an Oracle error to the app. (i.e. The app would see it as a
> successful insert). This is a quick fix while the developer fixes
> his/her app and on the long run this should not happen. I cannot stop
> all inserts to this table as 95% of the entries written are good ones
> (without nulls)
>
> Following is a quick trigger I came up with but gives me the "mutating
> error"
>
> CREATE OR REPLACE TRIGGER T_CST_TRAN AFTER INSERT OR UPDATE
> OF SSN ON CST_TRAN
> FOR EACH ROW
>
> BEGIN
> IF :new.SSN is NULL THEN
> delete from CST_TRAN where rowid=:new.rowid;
> END IF;
> END;
>
>
> Appreciate your help!
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Sep 21 1999 - 23:34:02 CDT