Trigger Help

[CassidyHunt]

I need to modify a trigger in my database. Before data is inserted into the table I need to see if it violates a constraint on the table. If it does then instead of inserting it I need it to update the existing row with the new data.

Currently the trigger looks like this:

CREATE OR REPLACE TRIGGER TBIUD_INVDIST_ROW 
BEFORE INSERT OR DELETE OR UPDATE 
ON INV_TRANS_DIST 
REFERENCING NEW AS NEWDATA OLD AS OLDDATA 
FOR EACH ROW 



BEGIN 
 IF DELETING THEN 
  plsql_INV_TRANS_DIST.ENTRIES := plsql_INV_TRANS_DIST.ENTRIES + 1; 
  plsql_INV_TRANS_DIST.table_IN_TRANS_ID(plsql_INV_TRANS_DIST.ENTRIES) := :OLDDATA.IN_TRANS_ID; 
  plsql_INV_TRANS_DIST.table_OUT_TRANS_ID(plsql_INV_TRANS_DIST.ENTRIES) := :OLDDATA.OUT_TRANS_ID; 
  plsql_INV_TRANS_DIST.table_DIST_QTY(plsql_INV_TRANS_DIST.ENTRIES) := (:OLDDATA.DIST_QTY * -1); 
  plsql_INV_TRANS_DIST.table_DEMAND_SUPPLY_ID(plsql_INV_TRANS_DIST.ENTRIES) := :OLDDATA.DEMAND_SUPPLY_ID; 
 END IF; 
 IF UPDATING THEN 
  plsql_INV_TRANS_DIST.ENTRIES := plsql_INV_TRANS_DIST.ENTRIES + 1; 
  plsql_INV_TRANS_DIST.table_IN_TRANS_ID(plsql_INV_TRANS_DIST.ENTRIES) := :NEWDATA.IN_TRANS_ID; 
  plsql_INV_TRANS_DIST.table_OUT_TRANS_ID(plsql_INV_TRANS_DIST.ENTRIES) := :NEWDATA.OUT_TRANS_ID; 
  plsql_INV_TRANS_DIST.table_DIST_QTY(plsql_INV_TRANS_DIST.ENTRIES) := :NEWDATA.DIST_QTY - :OLDDATA.DIST_QTY; 
  plsql_INV_TRANS_DIST.table_DEMAND_SUPPLY_ID(plsql_INV_TRANS_DIST.ENTRIES) := :NEWDATA.DEMAND_SUPPLY_ID; 
 END IF; 
 IF INSERTING THEN 
  plsql_INV_TRANS_DIST.ENTRIES := plsql_INV_TRANS_DIST.ENTRIES + 1; 
  plsql_INV_TRANS_DIST.table_IN_TRANS_ID(plsql_INV_TRANS_DIST.ENTRIES) := :NEWDATA.IN_TRANS_ID; 
  plsql_INV_TRANS_DIST.table_OUT_TRANS_ID(plsql_INV_TRANS_DIST.ENTRIES) := :NEWDATA.OUT_TRANS_ID; 
  plsql_INV_TRANS_DIST.table_DIST_QTY(plsql_INV_TRANS_DIST.ENTRIES) := :NEWDATA.DIST_QTY; 
  plsql_INV_TRANS_DIST.table_DEMAND_SUPPLY_ID(plsql_INV_TRANS_DIST.ENTRIES) := :NEWDATA.DEMAND_SUPPLY_ID; 
 END IF; 
END;

The constraint I am testing looks like this:

create unique index SYS_C002119_2 on INV_TRANS_DIST (IN_TRANS_ID, OUT_TRANS_ID)
  tablespace USER_INDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 9M
    minextents 1
    maxextents unlimited
  );

The table looks like this:

create table INV_TRANS_DIST
(
  IN_TRANS_ID       INTEGER not null,
  OUT_TRANS_ID      INTEGER not null,
  DIST_QTY          NUMBER(14,4) not null,
  ACT_MATERIAL_COST NUMBER(15,2) default 0 not null,
  ACT_LABOR_COST    NUMBER(15,2) default 0 not null,
  ACT_BURDEN_COST   NUMBER(15,2) default 0 not null,
  ACT_SERVICE_COST  NUMBER(15,2) default 0 not null,
  DEMAND_SUPPLY_ID  INTEGER
)

Thanks for all the help.

Cassidy

 

[Dagon]

I'm not sure what you're trying to achieve. You seem to be storing all the details in some sort of PL/SQL table, presumably for use in a statement level trigger at the end of the process. However, the insert will still take place and the statement will still fail because of the unique constraint violation, which means your statement level trigger won't be called.

For Oracle-related work, contact me through Linked-In.

 

[CassidyHunt]

The plsql table that is being used is at a trace level and I know it won't error. On the table above I would like to be able to check for the constraints on that table and if there is going to be a problem have the ability to solve those problems before they happen. I put that table up there because it is exactly what i am working with but just a sample of how to check if a particular constraint will be violated and if so how to stop the insert and change it to an update.

My main issue is that this is a dispute with my ERP provider. They say it isn't an issue but it stopping our costing from proceeding.

Thanks for the response.

Cassidy

 

[Dagon]

I don't think you can do that. Triggers are designed to work in conjunction with insert or update statements and they can't be used to transform one into the other. The only possibility would be to capture all the data in a PL/SQL table, as your doing, and then have some process which runs when an error is detected to go through all the rows which have been stored and work out whether they need to be updates or inserts. I don't think you could even do that with a statement level trigger because the statement level trigger wouldn't get fired if the insert failed.

For Oracle-related work, contact me through Linked-In.

 

[Thargy]

Cassidy,

Like Dagon, I can't figure out exactly what you're trying t do here. I believe that you are making some mistakes though.

First of all you are adding a unique index, and not a constraint. Drop the index immediately and add a unique CONSTRAINT, not an index. This is a classic blooper which will come back and bite you on the behind later. I suggest that you do something on the lines of

ALTER TABLE INV_TRANS_DIST ADD CONSTRAINT UQ_INV_TRANS_DIST__TRANS_IDS UNIQUE (IN_TRANS_ID, OUT_TRANS_ID);

The reason for this is manifold, but to name a few, a unique constraint can take part in referential integrity, whereas an index cannot. A unique constraint enables the optimizer to use query rewrite (assuming it is enabled) whereas a unique index does not. It is also highly confusing to not have a unique constraint visible on a table, and yet have duplicate entries fail.

Oracle will automatically create a unique index for the above constraint, but if the constraint is dropped, the index will silently go with it (automatically handled by Oracle) - the reverse is not true.
Second, you should not be validating your data in code, as that is a pointless activity which the db will do for you automatically with constraints. The only difference is that the db will always get it right, and pl/sql code won't.
Third, constraints are declarative and not procedural, so they are immune to temporal issues, whereas code most definitely is not.
Fourth, triggers may do unpredictable things. Six months from now, when someone attempts to insert data into the table, and the "wrong" data appears automagically, how will this "error" be debugged?
Fifth, if you know what the correct data is (and you must, otherwise how can you write the correcting code in the trigger?) then don't mess about, cleanse the data once and for all, and then add it to a constrained table.

Sixth, Oracle has superb built-in facilities for handling precisely this situation. You are writing dangerously bad code (i.e. because it's in a trigger) for no benefit. You could simply turn on dml error logging, and let Oracle do the job for you.

I hope you get my drift here, that's not a rant, just me letting off a bit of steam after a long day in the salt mines.

Regards

T

 

[CassidyHunt]

What I am doing is trying to fix what the vendor of our software won't fix. They claim it is working as designed. When in fact it errors out and kills user session which require me to go correct it. Major fight.

Instead of fighting I am trying to take their code in the triggers and index's they have to automatically detect the problem and proactively solve it before it crashes the end user.

This is just one example of poor design that I am forced to fix.

Thanks for the input. I will look at the dml error logging.

Cassidy

 

[Thargy]

Cassidy,

I admire your determination. Can you give a slightly clearer statement of what's going on, as we might be able to help.

I suggest that you be wary of 'fixing' the vendor's system for them, as once your software is involved, you give them the ultimate excuse of "our software's working fine, but your guy has intervened and broken it". If you can gather irrefutable evidence of their mistake, and then present it, you may do better.

By that I mean a fully scripted demo of what their system does and how it causes failure. At that point, even your most technically ignorant manager should see the point, and have a bash at the vendors.

Regards

T