Using SQL triggers on a database supporting an asp.net application

[Guest]

Hi - I can update a second field based on update of the the first field of
the same table. It works fine in the Enterprise Manager (EM), but I can't
seem to make the trigger fire when I alter the first field from the
application. Checking the table using EM, I can see the update in the first
field but obviously the trigger didn't fire because the second field is
still not changed. If I add "COMMIT" to the last line of the trigger, I get
database gets disconnected from the application (but the trigger fires).
What's going on?

 

[George]

It's a bit tricky when you work with triggers.

Let say you update 1 record.
UPDATE tblTable SET field1=5 WHERE Id=10
Trigger will fire once for that SQL statement


Now let say you update 10 records
UPDATE tblTable SET field1=5 WHERE Id <= 10

Guess how many time will trigger fire....?
Only once...
Because of that if your trigger written incorrectly it will update only
first record and not 9 others.

That is the only reason i can come up with why trigger seems to not firing..

George.

 

[Ten Gallon]

Thanks, George. I understand about that particular problem but I'm only
updating one record.

When I update one field, the trigger checks some conditions and updates a
second field. The first field is being updated, but the second is not
because the trigger is not firing when the update is done from the asp.net
program. When the first field is updated from the Enterprise Manager, the
trigger fires and updates the second field.



Anyone seen this before?

 

[Patrice]

My approach would be first to create the simplest possible trigger
(for exemple just inserting getdate() int o a trace table). The goal
is to really prove that the trigger doesn't work from ASP.NET wihich
is IMHO unlikely (ASP.NET doesn't know about this, this is handled
server side by SQL Server).

For now, the cause would be more likely that the test you have in
your trigger is not correct. In the worst case you should be able to
produce the simplest possible code that shows the problem and then
either find the problem or post the code for others to possibly spot
the culprit...