5

Looking for an example code in Oracle to do the same. i.e.

Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like?

What would I loose in the real world app by replacing an Insert trigger?

More clarification based on the responses below: We do not want Remote client to write to both table T1 and T2. This is a high throughput data and performance will suffer. Hence Remote client would write only to T1. Transfer from T1 to T2 should happen locally.

1
  • 1
    +1 Good instincts in thinking about removing triggers. They are useful but, I think, used too often. Because they tend to obscure logic, I don't think they should be used to implement business logic but are well suited to implement utility functions within a database (logging changes, etc).
    – ScottCher
    Feb 17, 2011 at 16:46

3 Answers 3

10

If T2 is derived data based on the contents of T1, then you may want to replace T2 with a materialized view. If T2 is rarely accessed, and quick to derive, then you may just want to use a view.

On the application side, if you stop maintaining table T2, then the values it gets will become increasing incorrect. This can be partially corrected by recalculating the derived values and updating T2 with the new values.

1
  • +1 I agree, a good alternative - especially when the logic applied to the data being inserted from one table to the other is simple enough for the materialized view to implement.
    – ScottCher
    Feb 17, 2011 at 16:44
8

Here is an example of the API structure to replace a trigger. By calling the AddEntry procedure in the package, the data is inserted into both tables.

CREATE OR REPLACE PACKAGE MyPackage IS
   Procedure AddEntry (pValue1 IN Number, pValue2 IN Number);
END;
/


CREATE OR REPLACE PACKAGE BODY MyPackage IS
   Procedure AddEntry (pValue1 IN Number, pValue2 IN Number)
   As
   Begin
      INSERT INTO T1 ("ID1") VALUES (pValue1);
      INSERT INTO T2 ("ID2") VALUES (pValue2);
   End;
END;
/

Switching to an API over a trigger is a good idea even if you decide that a view or materialized view is better than duplicating this data into a second normal table.

Update:

Based on the changes to the question indicating a desire to increase performance inserting into the T1 table here are some options.

  1. Use a materialized view for T2 as BillThor suggested.
  2. Create T2 as a view of T1.
  3. Create a job that periodically merges from T1 into T2.

A Bit more complex solution would be to create a message in a queue using Advanced Messaging with the Buffered Messaging feature instead of inserting into the table. You could then either have a job that periodically dequeues a bunch of messages and inserts them in bulk, or have a callback on the queue that calls the package to insert the data. This solution would likely have the best performance, but would also be the most sensitive to server failures with the messages stored in memory for a short time.

1
  • just updated the main question above based on the PL-SQL code here.
    – John
    Mar 10, 2011 at 6:21
6

It didn't seem like people were really addressing the second part of your question so I thought I'd chime in.

First, I like both answers to part 1 of your question. I think either a materialized view or creating a stored procedure to manage inserting into the second table are satisfactory (+1'd both of them). I like the flexibility of using a stored procedure better, however and believe using packages/procedures for your CRUD operations to be how most interaction with database structures should be handled. Your instinct to move away from Triggers is a good one, in other words - they have their uses but I think they tend to obscure business logic by placing it outside the direct method call stack (if you didn't know the trigger was there, you might not realize where its functions are coming from).

That being said, I move on to the second part of your question - "What would I loose in the real world app be replacing an Insert trigger?"

If you replace your trigger with a CRUD package and you don't control all of the input paths for inserting into your database such that they all use that CRUD package, you cannot guarantee that the logic previously embedded within your insert trigger will be activated on inserts.

Alternatively, if your trigger logic is simple enough to be replaced with a materialized view that was set to update on commit, you wouldn't have the problem outlined above, and would likely see no difference at all.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.