Validation in Datanase through statement Level trigger Options

[subhadra R menon]

Hi all,
I have a table which contains data of all calcualted payements for a Land for a particular period i.e. for a Land 124 for the duration of Jan-2010 to Mar-2010 100 rupees rent is paid to the owner.Requirement here is that for a particular duration i.e is for Jan-2010 to Mar-2010 system calculation of rent for a land file multiple times where the table would contain history of each transaction i.e. a user can calculate for a Land 124 for duration fo Jan-2010 to Mar-2010 rent more than ten times and there would be ten records in the table but only one record out of these would be valid where the user cannot calcualte rent for Lnd 124 for Jan-2010 to Mar-2010 without updating the status of the last record in the table for the same land and duration to invlaid.
I want to add this business validation through triggers as i am not able to do so through check constarinst.I want to ensure that only one record should be valid for a particular land for a particular duration
since row level triggers do not allow us to query the table ion which the trigger is executing i am trying to use statement level trigger but i have a few doubts here:
Incase of a Before Insert statement level trigger does the trigger have access to the data which is being inserted into that the table cause whenever a record is being inserted into a table the systen would check through the trigger that if there is a valid (payment_status is valid) payment record in the table for that same land file and same duration.If there is a valid record then the trigger would through an exception.
I know you can use a row level trigger with an autonomous transaction should work wihtout giving the mutating table error issue but i wont to know if using a statement level trigger here is feasible!!!!!!
Please do reply soon it is urgent

[RHR]

First, please be very cautious using the word URGENT in this forum. Having a production database down is URGENT and requires the prompt attention of a competent DBA. If it's truly URGENT and you cannot resolve it yourself or with the help of your DBA, you can call us a 800-766-1884 as we have a team of excellent DBAs available.

URGENT is not for homework, and our experts do not monitor this forum constantly.

Second, your question is pretty garbled (unclear) and you have not included any examples of what you have tried that is not working for you.

Now ... if you need more information about triggers:

General information about triggers is available here: http://dba-oracle.com/googlesearchsite_pro...le.com%252F#882

A bit of information about statement level triggers is available here: http://www.remote-dba.net/t_edb_triggers.htm

RHR

[subhadra R menon]

Hi,

All i wanted to know was if an Before Insert statement level trigger on a table would have access to the data being inserted into the table.
Here, the trigger would check if there is a record similar to the one being inserted already present in the table and if there is a similar record already present in the table then it would throw an exception to prevent duplicate records from being inserted into the table else the record would be inserted into the table.

But inorder to validate this the above mentioned statement level trigger needs to have access to the data being inserted causing the trigger to be fired.

Is this possible incase of a statement level trigger????
I cant use a row level trigger to validate this cause i cant query the mutating table in row level trigger.

[burleson]

Hi Subhadra,

>> Here, the trigger would check if there is a record similar to the one being inserted already present in the table

It's considered a faster, and a better practice to try the insert and then trap and ignore the "row already exists" error in your OTHERS clause . .


***********************************************
>> Before Insert statement level trigger on a table would have access to the data being inserted into the table

Yes, if you write it that way!

A "trigger" is just PL/SQL that is tied to a user event . . .

If you write the trigger to accept the insert values as arguments, you have all you need!

[subhadra R menon]

But my question is using what varaible or statement would the trigger have access to these values of the insert statement.....
How will the trigger be able to access values being inserted into the table through an insert statement which causes the BEFORE INSERT statement level trigger on that particular table to be fired in the first place.Cant use new or old value references as incase of row level triggers.
Please do advice cause i am in dire need of this information.......
Also if you could share sameple code of such a statement level trigger then i would be highly obliged.

[orabase]

But my question is using what varaible or statement would the trigger have access to these values of the insert statement.....
How will the trigger be able to access values being inserted into the table through an insert statement which causes the BEFORE INSERT statement level trigger on that particular table to be fired in the first place.Cant use new or old value references as incase of row level triggers.
Please do advice cause i am in dire need of this information.......
Also if you could share sameple code of such a statement level trigger then i would be highly obliged.

Hi,
I am not much aware, that can be possible in statement but you can use row level trigger.
To avoid the mutating error:
create a package variable,
create a before row level trigger and assign the new values to the package variable,
create a statement level trigger and use the package variable inside it which has a new values.

thanks
oracle learner

[subhadra R menon]

I think as per trigger execution order before statement level trigger will be executed first anf then before row level triiger so i think this would not be helpful.

[burleson]

Hi Subhandra,

>> But my question is using what varaible or statement would the trigger have access to these values of the insert statement

A trigger is called as an autonomous transaction at insert time . . .

If you read the docs you will see:

For each of these triggering events, Oracle Database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).

Just another reason why trying to use a trigger to validate data is a BAD idea!

Conceptually, I strongly disagree with using a before insert trigger to validate data!

It's NOT a GOOD IDEA!!!!

Instead, explore using regex yo validate data formatting:

http://www.dba-oracle.com/t_regular_expres...tes_columns.htm

And use check constraints to validate:

http://www.dba-oracle.com/t_oracle_check_constraint.htm

If you MUST do complex validation priot to an insert, write a stored procedure called insert_into_mytab, and call it in lieu of the native insert statement!

[subhadra R menon]

Hi,
Sadly i cannot implement this validation using both regex and check constarint and neither through the database design.
I have placed this validation through code (in code which would input data into this table) but persoanlly i believe it is not sufficient enough as this validation should be present through some kind of check in the system which would exists somewhere other than code.
I know using triggers to reject bad data is not a good practice and i am suing the same as a last resort and it si suggest what cannot be implemented through check constraint in the system triggers must be given a second thought over ther.

And in your earlier comment we can write procedure or codes which can be used to recive this insert statement data.
Or should i drop this plan of adding validation anywhere other than code altogether

[burleson]

Hi Sub,

>> Sadly i cannot implement this validation using both regex and check constarint and neither through the database design.

If you are not allowed to do it right, refuse to do it!

Personally, I would never tarnish my reputation by performing an Oracle bad practice, even if it cost me my job.

Besides, there is NO REASON why you cannot add check constraints , , , ,

**************************************************
>> this validation should be present through some kind of check in the system which would exists somewhere other than code.

This makes no sense! Validation IS CODING!

Your choices are:

- Use constraints for data validation
- Make a stored procedure and call the procedure INSTEAD of using a native insert statement
- Make a validation stored procedure and make sure that everybody includes it in their PL/SQL before issuing the insert . . .
- Use the before insert trigger

That's it! It's ALL DONE BY CODING!

Good Luck!

[memberOne]

Any reason why validation in triggers is not recommended? Appreciate your advice.

[burleson]

Hi,

>> validation in triggers is not recommended?

Lots of validation is done with constraints, like "check" constraints.

Triggers are very problematic:

- mutating table errors

- bad design issues

Instead of a "brefore insert" trigger, simply write a independent before insert stored procedure and call it immediately before issuing the insert/update.

Please read:


http://www.dba-oracle.com/t_trigger_data_validation.htm


http://www.dba-oracle.com/m_trigger.htm