Re: Check on field when modified

From: AcCeSsDeNiEd <dillon_at_rm_accessdenied.darktech.org>
Date: Tue, 21 Jan 2003 09:49:54 +0800
Message-ID: <6o9p2v4gt7dlvnbdo9tp4lvhhpohdk50vb@4ax.com>

Sorry if this post has been doubled posted. I don't see my post in the ng.

---

Sorry should have given more details.
What I need is to make sure my application vendor always updates the "Mod_De_Emp_Id" (Modified Date-Entry Employee ID) column for the record that is being modified. It is nullable on inserts (I have another field "De_Emp_Id" that is not null).

Is there a way in the trigger that I can check whether an input for "Mod_De_Emp_Id" has been made? Maybe something like:

IF :input.Mod_De_Emp_Id is null

      THEN RAISE_APPLICATION_ERROR(-20110, 'Need a Mod_Emp_Id for Modifications.');     END IF; I have searched the net but did not come up with much. Made something up.
This is half-working:

BEGIN

	IF :new.Mod_De_Emp_Id is null
		THEN RAISE_APPLICATION_ERROR(-20110, 'Need a Mod_Emp_Id.');
   	 END IF;
    
	IF :NEW.Mod_De_Emp_Id = :OLD.Mod_De_Emp_Id
		THEN RAISE_APPLICATION_ERROR(-20110, 'Need a Mod_Emp_Id for Modifications.');
    	END IF;

END; The above works fine except that if the same employee is did the data-entry for both the old and new records, we have a problem. It's gonna out an error for a genuine update.

Any pointers?

On Sun, 19 Jan 2003 17:35:30 +0100, Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote:

>use a before update for each row trigger to check whether
>:new.<column_name is not null and :old.<column_name> is null
>
>Though I think you need to provide more detail to get a more precise
>picture of your needs.
>
>
>Sybrand Bakker, Senior Oracle DBA
>
>To reply remove -verwijderdit from my e-mail address

To e-mail me, remove "rm_" Received on Mon Jan 20 2003 - 19:49:54 CST