Can some one please explain how the target table locking applies in statement vs row level triggers.
I have created a statement trigger on a landing zone table (L) which will insert a record into target table (t) based on column value for each row.
Now to access the target table (t) as soon as possible in real time which one is perfereable statement or row level trigger?
Is statement level trigger will lock the target table until it completes the transaction for all the rows? Is it full table lock or rowhash level lock ?
According to your sayings, you need a row trigger. Just try to make it single AMP on the target table and you'll be fine.
Keep in mind triggers are ok in an OLTP application, but very rarerly usefull in DWH environnement.
You also must challenge the whole trigger mechanism versus a join / hash index or even a view.