CODEWITHSUNDEEP
sqloracle-database
hyeri
hyeri

Reputation: 693

Trigger not firing (compiles successfully)

I have these two tables 

CREATE TABLE Car_Makers ( 
ID NUMBER(5), 
Maker VARCHAR2(15), 
FullName VARCHAR2(25), 
Country NUMBER(5),
MODAL_TOTALS INT, 
CONSTRAINT car_maker_country_id_fk FOREIGN KEY(Country) REFERENCES 
 Countries(CountryID), 
CONSTRAINT car_maker_id PRIMARY KEY(ID) 
 );

CREATE TABLE Model_Details ( 
ModelID NUMBER(5), 
Maker NUMBER(5), 
Model VARCHAR2(25), 
CONSTRAINT model_unique UNIQUE(Model), 
CONSTRAINT model_details_id_pk PRIMARY KEY(ModelID), 
CONSTRAINT model_maker_fk FOREIGN KEY(Maker) REFERENCES Car_Makers(ID) 
);

I want to create a trigger that will update MODEL_TOTALS IN Car_Makers WHEN a new row is inserted in Model_Details. However my triggers runs successfully but is not firing. I don't get any error but when i insert a row in Model_details the corresponding MODEL_TOTAL value doesn't update. Any help will be appreciated, here is my trigger 

   CREATE OR REPLACE TRIGGER trg_modeltotals_up 
   AFTER INSERT ON MODEL_DETAILS
   REFERENCING NEW AS NEWROW
   FOR EACH ROW
   BEGIN
   UPDATE CAR_MAKERS SET MODEL_TOTALS = MODEL_TOTALS+1 WHERE 
   CAR_MAKERS."ID" = :NEWROW.MAKER;
    END;

Upvotes: 0

Views: 47

Answers (2)

hotfix
hotfix

Reputation: 3396

you trigger is not valid because, you made a mistake . The column is MODAL_TOTALS and not MODEL_TOTALS

So that the column MODAL_TOTALS always get a value in Car_Makers you can addthe a not null constraint or maybe set a default value to 0

MODAL_TOTALS INT not null,

or

MODAL_TOTALS INT Default 0,

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

Probably, nulls are causing the issue. Add a coalesce in your update

UPDATE CAR_MAKERS SET MODAL_TOTALS = COALESCE(MODAL_TOTALS,0) +1 WHERE 
   CAR_MAKERS."ID" = :NEWROW.MAKER;

DEMO

Upvotes: 1

Related Questions