Avatar of Angela Valiente
Angela Valiente
 asked on

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

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

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
Nitin Sontakke

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 Sontakke

Or may be simply:

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

Open in new window

Angela Valiente

ASKER
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'
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
alsatham hussain

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;
ASKER CERTIFIED SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Angela Valiente

ASKER
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 hussain

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 ;
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Angela Valiente

ASKER
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 hussain

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 hussain

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Helena Marková

alsatham hussain is right. It is a good practice to avoid double quotation marks (") in object's definition.
johnsone

Did you at least attempt to try what I posted?  It compiled just fine on my machine and did what you wanted it to.
slightwv (䄆 Netminder)

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.