troubleshooting Question

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

Avatar of Angela Valiente
Angela Valiente asked on
Oracle Database
13 Comments1 Solution1221 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
johnsone
Senior Oracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros