Link to home
Start Free TrialLog in
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.
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

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

Avatar of Angela Valiente
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'
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
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ;
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 ;
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 ;
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
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.
alsatham hussain is right. It is a good practice to avoid double quotation marks (") in object's definition.
Did you at least attempt to try what I posted?  It compiled just fine on my machine and did what you wanted it to.
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.