We help IT Professionals succeed at work.

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

Angela Valiente
on
1,210 Views
Last Modified: 2018-02-02
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

Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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

Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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'
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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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
CERTIFIED EXPERT

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

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.