Thread: Trigger Advice

    #1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2005
    Location
    Manchester
    Posts
    395
    Rep Power
    23

    Red face Trigger Advice


    Hi

    I am soon engaging in a project where we will deploy three triggers to each table in a customers live database. One each for Inserts,Updates and deletes.

    These triggers write or update a row in a separate table on a different schema.

    We have experienced locking issues on these tables previously with SQL Server.

    I am simply looking for advice on what to look for if issues occur around performance or locking and anybody else's experience with trigger performance/locking issues on Oracle?

    Are there settings we can look at to improve performance?
    Any general guidance and advice is much appreciated from anyone who has worked in this kind of area.

    Thanks in Advance
    Will Code for Rep!
  2. #2
  3. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    119
    First of all if you are working on a single source table, you do not need 3 separate triggers. It can be achieved with a single trigger by handling different DB events. and since the triggers write or update a row in a separate table on a different schema, you need to check for the commit mechanism that is implemented, may be through your application. Also you need to verify the dependency of data across multiple tables / schemas or any other existing triggers.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2005
    Location
    Manchester
    Posts
    395
    Rep Power
    23
    Thanks for your reply, our application automatically creates the separate triggers so there is no choice there unfortunately. There are hundreds of tables to each have these triggers.

    What do you mean regarding checking the commit mechanism?

    My main concern is for any performance issues we could hit and not knowing which settings to change if any performance issues did occur. Or locking of the tables for that matter.
    Will Code for Rep!

IMN logo majestic logo threadwatch logo seochat tools logo