For Each Row Trigger

Hi,

Does anybody know how could I define a for each row trigger in sql Server 2005?

What I need to do is before inserting in the table look through the rows to check wheter that value overlaps with the others.

"Overlap" means that this value cannot be betweent the values fo two different columns, that is why I need to go through all the table.

In case that was not possible, I propose the following, load the table in a dataset and check the constraint with a for loop despite of the performance could be decreased.

Any suggestion?.

Thanks in advance.

Cheers.

You could use an INSTEAD OF INSERT trigger, and then have the trigger only insert the values that match your criteria. I'm not sure how your table is set up, but something similar to the following:

CREATE TRIGGER CheckInsertON Test_Table2INSTEADOF INSERTASBEGININSERT INTO Test_Table2("Time", InsertNumber)SELECT "Time", InsertNumberFROM INSERTEDWHERENOT EXISTS(SELECT InsertNumberFROM Test_Table2WHERE "Time"BETWEEN i."Time" AND i."Time2")END

 
The above query will only insert the records that don't fall between another record.
If you just want the insert to fail, and not insert any records, you could just check if there are any matches, and then return an error if there are.

|||

Hi, thanks for the reply,

That makes sense, I am going to try it and I will get you back whether it works or not.

Cheers.

|||

Hi, well I have defined the trigger and I think that it fires but I get an error during the insertion:

Cannot insert explicit value for identity column in table 'Items' when IDENTITY_INSERT is set to OFF. The statement has been terminated.

I dont know how to set this property...,

my trigger is the following in case it could help you.

ALTER TRIGGERtrgCheckItemInsert

ONdbo.Items

INSTEAD OF INSERT

AS

BEGIN

INSERT INTOItems(itemID,itemName,rackID,itemNumberOfUnits,startU,endU,itemDesc)

SELECTi.itemID,i.itemName,i.rackID,i.itemNumberOfUnits,i.startU,i.endU,i.itemDesc

FROMINSERTED i

WHERE NOT EXISTS(

SELECTstartU

FROMItemsINNER JOINRacksONItems.rackID = Racks.rackID

WHEREItems.rackID = i.rackIDANDItems.itemID != i.itemID

ANDi.endU > Racks.numberOfUnitsANDi.startUBETWEENItems.startUANDItems.endU

)

END

any suggestion?

Thanks

|||

Hi, this problem is solved, I just had to remove the itemID from the insert statement.

Thanks.

|||

Well, finally the trigger doesnt fire, I have put a RAISERROR ('Hello',1,1) and it is not working, I catch the exceptions while the insertion between a try catch block and normally when I get an error from the database like duplicate id for the primary key or whatever I can catch it, but the raise statement I cant.

How could I ensure that the trigger fires? because moreover I am still able to insert worong values.

Thanks.

|||

I tried putting RaiseError in the first line of the trigger, and then ran it from the SQL Management Studio, and the rest of the trigger still ran, so I'm not sure if it will throw an exception in .NET. You could place a ROLLBACK TRANSACTION as the last line in the trigger, or else just comment out the INSERT command, and then see if anything is inserted into the table. Either of those should stop the insert, so you could at least tell if the trigger is running.

My guess is that the trigger is firing, but something in the WHERE clause is never evaluating to True, so that the NOT EXISTS always returns true. To test that, you could take the SELECT statement that you have in the EXISTS function, and see what it returns when you replace all the i.Columns with values that should find duplicates. Then see if any records are found. I don't know what your table schema looks like, so I'm not sure, but a couple of possibilities from the select statement below.

SELECTstartU

FROMItemsINNER JOINRacksONItems.rackID = Racks.rackID

WHEREItems.rackID = i.rackIDANDItems.itemID != i.itemID

ANDi.endU > Racks.numberOfUnitsANDi.startUBETWEENItems.startUANDItems.endU

I don't think you need the "Items.itemID != i.itemID" because i.itemID is an AutoIncrement field, so that will never evaluate to false (I don't even know if i.itemID has a value at this point). That line shouldn't change the outcome, it just isn't needed.

the line "i.endU > Racks.numberOfUnits" sounds like it is checking for a condition you want met for the insert (This is in a NOT EXISTS, so you should only have conditions you do NOT want met). If that evaluates to FALSE even though i.startU is between startU and endU, you still may not get any results. I'm guessing you either need an OR instead of an AND, or else just check for the start and end dates and get rid of that check.

I wonder if you could rewrite the select command as

SELECTstartU

FROMItems

WHEREItems.rackID = i.rackID

ANDi.startUBETWEENItems.startUANDItems.endU

|||

Thanks for the reply,

well finally I got the solution, the trigger was wrong at some conditions and finally I fixed it.

The problem now arises when I define the INSTEAD OF UPDATE TRIGGER, because when I raise the error I cant catch it at any event, I try to catch it within the event Grid_Row_Updating and it doesnt work, and the same in the Row_Updated event, so I get an unhandled exception.

Do you have an idea about that?.

Cheers.

|||

It depends on how you are using the DataBinding. Are you calling DataBind() explicitly, or are you binding the GridView to the DataSource in the aspx page? The DataBind() method is where you need the Try-Catch. If you can bind the data to the GridView in the Page_OnLoad, that should let you catch the exception. I don't know if you can catch the exception if you are setting the DataSource in the aspx page.