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.
Angela ValienteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
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

0
Nitin SontakkeDeveloperCommented:
Or may be simply:

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

Open in new window

0
Angela ValienteAuthor 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'
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

alsatham hussainOracle developerCommented:
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;
0
johnsoneSenior Oracle DBACommented:
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.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angela ValienteAuthor 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 ;
0
alsatham hussainOracle developerCommented:
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 ;
0
Angela ValienteAuthor 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 ;
0
alsatham hussainOracle developerCommented:
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
0
alsatham hussainOracle developerCommented:
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.
1
Helena Markováprogrammer-analystCommented:
alsatham hussain is right. It is a good practice to avoid double quotation marks (") in object's definition.
0
johnsoneSenior Oracle DBACommented:
Did you at least attempt to try what I posted?  It compiled just fine on my machine and did what you wanted it to.
0
slightwv (䄆 Netminder) 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.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.