Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Problem (Oracle 11g)
Trigger Problem [message #421323] Mon, 07 September 2009 08:32 Go to next message
TonyJ
Messages: 1
Registered: September 2009
Junior Member
Can anybody help with this trigger problem. I need a trigger whenever a taxreturn is added or the ADJGrossIncome field is updated
It should compute the tax rate and put it in the IncomeTaxes field

taxreturns table has 3 columns SSN, ADJGROSSINCOME, TaxReturn
taxrate has 3 minincome, maxincome, taxrate

Not sure if I m on the right track here or not. Any help is greatly appreciated.

Create Trigger TAXRETURNCHANGES
Before Insert or Update of ADJGROSSINCOME On TaxReturns
For Each Row
DECLARE
minIncome Number;
maxIncome Number;
taxRate Number(3,2);

BEGIN
select minIncome, maxIncome, taxRate into minIncome, maxIncome, taxRate from TaxRates
where ADJGROSSINCOME = :new.ADJGROSSINCOME;
If (ADJGROSSINCOME > MININCOME & ADJGROSSINCOME < MAXINCOME) Then
IncomeTaxes := ADJGROSSINCOME * taxRate

end if;
Insert into TaxReturns (SSN, ADJGROSSINCOME, INCOMETAXES)
Values (SSN,ADJGROSSINCOME,INCOMETAXES);
END;
Re: Trigger Problem [message #421324 is a reply to message #421323] Mon, 07 September 2009 08:43 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
First off - when posting code can you please use code tags - see the orafaq forum guide if you're not sure how.

Some points on the trigger:
1) Variables should be typed to columns wherever possible.
2) The select might need exception handlers to protect against no_data_found and too_many_rows.
3) Whenever you reference columns in the table the trigger is on you need to use :old or :new.
4) DB Triggers don't override the original DML statement so that insert statement at the end is unecessary and will lead to an infinite loop - or more likley an error.

Have a read of this if you haven't already - triggers

More generally - if a field is calculated then it's usually better not to store it at all. Instrad just calculate it when you need it.
Re: Trigger Problem [message #421365 is a reply to message #421323] Mon, 07 September 2009 20:38 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I would also add that if you are new to triggers, you should do some more research and testing with them before you jump into them. Especially if this is an existing system you are modifying and very much so if this existing system does not yet have any appreciable trigger implementation yet.

I love triggers (in particular INSTEAD-OF-TRIGGERS), but I would say that most people do not really understand triggers and how to use them correctly. To use triggers well, you must appreciate some PHILOSOPHY. Without an appreciation of the nature of triggers and for the implications of their use, you will like most people eventually get into trouble. Let me offer up some ideas for you to think about:

There are three ways triggers are used, only one really works well:

1) As HACKS for a quick problem fix. This is how most people use triggers and why they get into trouble and why triggers have gotten a bad rap in some circles. A programmer is looking for a quick way to fix a problem and they deduce that they can use a trigger to make things work without having to change any application code. Now normally not having to change application code to get something done would be a good thing but in this case it is very bad because what you have done is split up the logic of the application into two places without any agreement amongst everyone using the database that this is what you want to do. VERY BAD because later someone will want to change application logic for some reason but the application logic that needs changing will not be in the application and because there is no architectural agreement on how application logic should be split up, they won't know to look in the database for what is missing. Can't think of a better way to make people mad, and to create problems then to hide application logic that should not be hidden.

2) As a design strategy for splitting up application logic between application layer and database layer. This is better than #1 above mostly because although it is essentially the same process, there has been a concious decision at the architectural level of database design and application design, to create this division of logic. In practice this is somewhat difficult to achieve well because of the need to decide how application logic should be split such that a clear reasoning emerges as to why some logic was put into the application layer and other logic was put into the database layer. Additionally this kind of system requires aggrement between all who use it that this split exists. Only an outfit willing to monitor development on this data delivery vehicle for its lifetime can reasonably hope to getthis split to work.

3) As an invisible implementation of behaviors in the database. This is where triggers really shine and how they should be used. Invisible in our discussion here is important to understand. Logic in database triggers should not be required in order to get the business goals done. If you can remove the database behavoir implemented in triggers and still have a functioning business application then your use of triggers is likely well done. If you cannot remove the triggers and thus the logic within them and still have a working business application, then your trigger implementation is not invisable. As an example consider auditing of data changes within the context of an employee maintenance business function. What does auditing have to do with adding an employee to the system. It actually has nothing to do with it. The same application can add the employee without creating audit data or with auditing the add. Both solutions satisfy the business need. Of course without the auditing, there is no traceability of the event but that is a different question from implementing the business function that the application has been tasked to implement. If we accept this idea then the next logical question is why should the application be forced to audit its changes at all. It is not a requirement of the application, so why are we forcing the application to do this extra work, It is in fact a requirement of needs and goals external to the business process. With this in mind we would want to write our applcation without any auditing logic, and have the database do it for use without us knowing about it. This is a very good thing because for one thing it means our applications do not have logic in them that is not relevant to the business process they are implementing. But how do we do it? Enter triggers.

As a general rule, triggers should not be used to implement application logic, e.g. triggers should not be part of the implementation solution for a business process.

As a general rule, triggers should be used to implement data behaviors not necessary to the implementation of the business need. Said another way, triggers can be used to implement logic that business processes are not responsible for and do not want to know about. If the logic is not part of the business process, then it is a candidate for possible trigger implementation.

There is great benefit in the invisible implementation of data behavior. Everyone application uses it, no application has code for it, whatever is being implemented is implemented only once so it performs the same way everywhere. New applications automatically get it, old applications automatically get it, nobody can avoid it, ... and lots of other reasons.

So what does all this have to do with you?

What you describe above sounds to me like it is business logic you are trying to do. That means using trigger will cause a split of the logic in your application system. It sounds like you are thinking of doing #1 above, the worst of ideas. If you remove the trigger code, will your app still work? If not then you have split your business process between the application and the database which is generally a bad thing to do.

If you disagree, please say so and we can discuss further.

Good luck, Kevin

[Updated on: Mon, 07 September 2009 21:26]

Report message to a moderator

Previous Topic: Comparing AUDIT TABLE values
Next Topic: decode or if else
Goto Forum:
  


Current Time: Sat Dec 10 08:42:00 CST 2016

Total time taken to generate the page: 0.04608 seconds