Trigger to update a specific column in Table B after insert or update record from Table A

Angela Valiente
Angela Valiente used Ask the Experts™
on
I'm using an Oracle database and need some help - triggers are something I struggle to understand.

I need a trigger for when I insert or update a row in Table A so that it updates a column on Table B.

Table A includes:

 CREATE TABLE "RELEASE_RECORD"
   (      "LABNUMBER" VARCHAR2(20 BYTE) NOT NULL ENABLE,
      "RELEASED_BY" VARCHAR2(15 BYTE) NOT NULL ENABLE,
      "DATE_TIME" DATE,
       PRIMARY KEY ("LABNUMBER", "RELEASED_BY")
) ;


Table B includes:

CREATE TABLE "AUTO_EMAIL"
   (      "LABNUMBER" VARCHAR2(20 BYTE),
      "DATE" DATE,
      "EMAIL" VARCHAR2(1000 BYTE),
      "STATUS" VARCHAR2(1 BYTE),
       PRIMARY KEY ("LABNUMBER")
);



And I have a trigger like this:

create or replace trigger TRG_AUTO_EMAIL
AFTER INSERT or UPDATE on RELEASE_RECORD
FOR EACH ROW

DECLARE v_labnumber  number;
BEGIN

UPDATE AUTO_EMAIL
  set STATUS = 'N'
  WHERE labnumber = v_labnumber;
END ;


Everytime I insert or update a record in RELEASE_RECORD table, I want the status in AUTO_EMAIL update into "N" automatically.

But it seems that my trigger did not work.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I am NOT  a PL/SQL developer but let me shoot in the dark:

DECLARE v_labnumber  number;
BEGIN

SELECT :new.LABNUMBER INTO  v_labnumber  from dual;   --Try adding this line....

UPDATE AUTO_EMAIL 
  set STATUS = 'N'
  WHERE labnumber = v_labnumber;
END ;

Open in new window

Or may be simply:

UPDATE AUTO_EMAIL 
  set STATUS = 'N'
  WHERE labnumber = :new.labnumber;
END ;

Open in new window

Author

Commented:
Got these error using the given code above.

Error(3,8): PLS-00049: bad bind variable 'NEW.LABNUMBER'
Error(7,20): PLS-00049: bad bind variable 'NEW.LABNUMBER'
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

alsatham hussainOracle developer

Commented:
Hi,

I am not sure,
can you please try:

CREATE or REPLACE TRIGGER TRG_AUTO_EMAIL
AFTER INSERT or UPDATE on RELEASE_RECORD
FOR EACH ROW
DECLARE
BEGIN
update AUTO_EMAIL
set STATUS = 'N'
where labnumber= :old.labnumber;
DBMS_OUTPUT.PUT_LINE('STATUS  successfully updated into AUTO_EMAIL  table');
END;
Senior Oracle DBA
Commented:
This works:
CREATE OR replace TRIGGER trg_auto_email 
  BEFORE INSERT OR UPDATE ON release_record 
  FOR EACH ROW 
BEGIN 
    UPDATE auto_email 
    SET    status = 'N' 
    WHERE  labnumber = :new.labnumber; 
END; 

/ 

Open in new window

Keep in mind that the way you have this written is if you are doing an insert and no record exists in the AUTO_EMAIL table, the update isn't done.  Your application would have to make sure that you insert into AUTO_EMAIL before inserting into RELEASE_RECORD.

Author

Commented:
Still got this error after compiling the trigger.

Error(4,21): PLS-00049: bad bind variable 'NEW.LABNUMBER'



CREATE or REPLACE TRIGGER TRG_AUTO_EMAIL
AFTER INSERT or UPDATE on V_EVENTLOG_TESTING
FOR EACH ROW

BEGIN
UPDATE V_AUTO_EMAIL_TESTING
  set LAB_RESULT_FLAG = 'N'
  WHERE labnumber = :new.labnumber;
 
END ;
alsatham hussainOracle developer

Commented:
Hi,

I am not sure,

I think, you are  misspelled table name on update area.

Please use the table name "auto_email" instead of "V_AUTO_EMAIL_TESTING "

BEGIN
UPDATE AUTO_EMAIL_TESTING
  set LAB_RESULT_FLAG = 'N'
  WHERE labnumber = :new.labnumber;
 
END ;

Author

Commented:
Sorry wrong pasting.. Got a little bit confused.

Here it is.


Error(4,21): PLS-00049: bad bind variable 'NEW.LABNUMBER'


Using this code:

CREATE or REPLACE TRIGGER TRG_AUTO_EMAIL
AFTER INSERT or UPDATE on RELEASE_RECORD
FOR EACH ROW

BEGIN
UPDATE AUTO_EMAIL
  set STATUS  = 'N'
  WHERE labnumber = :new.labnumber;
 
END ;
alsatham hussainOracle developer

Commented:
Hi,
I think I found,

Please use this

BEGIN
UPDATE AUTO_EMAIL
  set STATUS  = 'N'
  WHERE LABNUMBER =:NEW."LABNUMBER";
 
END ;

Look here: https://community.oracle.com/thread/941756
alsatham hussainOracle developer

Commented:
A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.
Helena Markováprogrammer-analyst

Commented:
alsatham hussain is right. It is a good practice to avoid double quotation marks (") in object's definition.
johnsoneSenior Oracle DBA

Commented:
Did you at least attempt to try what I posted?  It compiled just fine on my machine and did what you wanted it to.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Double quotes on an all upper case object name doesn't do anything.  Oracle defaults everything to upper case.

>>Error(4,21): PLS-00049: bad bind variable 'NEW.LABNUMBER'

I know you posted a create table statement.  Double check the actual table RELEASE_RECORD and verify the column name is really LABNUMBER.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial