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

A copy of this was sent to jsenarat_at_my-deja.com (if that email address didn't require changing) On Tue, 21 Sep 1999 23:48:21 GMT, you wrote:

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

this sounds dangerous -- who knows what side effects this could have elsewhere in the application -- or to the end user running it. Data they are sure they put in there mysteriously disappears. doesn't sound like a good idea.

anyway, to get around the mutating table error, see the url in my signature below. there is a paper on that.

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

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 22 1999 - 10:18:40 CDT