Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 '11 at 16:46
add comment

3 Answers

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.

share|improve this answer
    
+1 This answer is useful given the modifications to the question. –  Leigh Riffel Feb 14 '11 at 21:43
    
+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 '11 at 16:44
add comment

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.

share|improve this answer
    
+1 Never underestimate the power of CRUD. –  ScottCher Feb 17 '11 at 16:43
    
just updated the main question above based on the PL-SQL code here. –  John Mar 10 '11 at 6:21
add comment

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.

share|improve this answer
    
+1 Thanks for catching what we missed. –  Leigh Riffel Feb 17 '11 at 18:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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