Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Feline
Starting Member
5 Posts |
 Posted - 2002-10-11 : 05:18:37
|
| Hello people I've been useing this site for a liilte while now and most of the time can find the ans to what I need in the pages forums V good by the way, but I'm unable to find this one, it would be most aprecated if you could help me.right here is the play.I have a table which has buisness logic aplyed to it that could not be done with a check constaint, hence I use a trigger. In the trigger I delete any rows which violate a rule, I also roll back the transaction if the block violates a another rule. The problem is that I return -1 , 0 from all my stored procedures this means I can indecate to the user succress or failer. However if I delete an invaled row in the trgger how do I know I've done this. I use @@error to check for error after the insert and so should raise an error and chatch that or is there a better. The other thing I thought of was to put the test logic in the stored procedure before the insert but there a lots of way this table can be altered hence much harder to maintain than just using the one insert - update trigger.Any surgertions or advice pleaseP.S. sorry about the spelling thats just meEdited by - Feline on 10/11/2002 05:19:15 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-11 : 06:22:44
|
| That's quite interesting.How does an SP find out what a trigger has done?Usually a trigger maintains things behind the scenes and at the higher level you just want to know whether your operation has succeeded or failed.This sounds a bit like a design problem - putting application functionality at too low a level.I can think of a few mechanisms but none fun.Create a table with a few rows - the trigger then updates the table as it's last statement.It updates a number of rows depending on the action.The sp then uses @@rowcount as the return code.- note this will lock the update table and is not a good idea anyway.Create a table keyed on spid into which the trigger can put the return code.The trigger will have to add the spid entry if it doesn't already exist.Have an audit trail (with a trigger) for the table and the SP queries the audit trail table.Needs spid + logon time to do this.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Feline
Starting Member
5 Posts |
Posted - 2002-10-11 : 07:20:12
|
| I would like to move the check code to else where but the application that I'm desiging is a booking system. If I move the logic to a higher level then then I will lock the table for to long I think.My think is this person creates a booking in GUI on curent data i.e. there is room to book in then they send the request to the server. However in the mean time some one else could have booked that solt. Hence the request to book then if the space is there book if not replie to user. AS there are several difrent ways to book i.e. person (GUI) or XML from other system hence I have sevral procedure that could do the job there for a trigger, I could move the trigger into amn sp and then run the sp on the incoming data before the instert.Is this a better than the extra tables but it dose mean I'll have a more complicated system to maintain and and people will be able to side step the rules use there own SP's or table edits in enterprizeBeer do I detect a touch a ale or bitter (I'm a northener) |
|
|
|
|
|
|
Validation in trigger is it happend or not