If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

As far as I understand, triggers are invoked when data modification events happen to the table with which the trigger is associated.

The table in question (essentially a user table) has 3 triggers on it, doing similar checks which result in updating a separate table (essentially activity type tracking). The trigger I'm concerned with is "for insert", as it doesn't seem to to be invoked in all the cases it needs to. Here are the cases:

- A php website using the table does inline insert commands (invokes the trigger every time).

- A .NET 2.0 webservice uses a stored procedure to insert into the table (which seems to have only invoked the trigger once).

- A separate MS Access UI at another physical location directly connects to & inserts into the table (which seems to have never invoked the trigger).

Exactly! We had to enforce business rules with triggers, since there is a MS Access front-end UI that directly connects to the members table that needs to have user activity rtracked, as well. Since this trigger was the solution opted for by the client, it necessitated the removal of code in the website & stored procedure to avoid duplicate action tracking entries.

Are you saying that the criteria in the where clause might be invalid?

You can construct and script a profiler trace that will only capture INSERTs into the table. When selecting events make sure to include RPC calls. In the columns include HostName along with loginname and application name, to be able to distinguish where the INSERT is coming from. Once the trace is captured and collected, you can examine the statements that are coming from Access UI to see what gives. As per procedure - unless Deleted is defined with DEFAULT constraint, your WHERE clause in the INSERT trigger will always return 0 rows, meaning 0 rows will be affected, because Deleted will have NULL in the INSERTED virtual table.

"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."

Assuming the answer is no, I would suggest changing your approach to abstracting data access through stored procedures AND ONLY stored procedures, then homebrewing whatever historical records you want to track. Triggers have a way of creating maintainability/predictability issues further down the line.

Assuming the answer is no, I would suggest changing your approach to abstracting data access through stored procedures AND ONLY stored procedures, then homebrewing whatever historical records you want to track. Triggers have a way of creating maintainability/predictability issues further down the line.

All good ideas, but I think the OP is operating within the customer's constraints. Apparently they love their little MSAccess app, and want to make sure that the vendor (the OP) can cope with the task of tracking the activity generated by it as well.

"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."

Yes. Triggers fire regardless of the initiator of the transaction. There is nothing in that trigger that would prevent it from firing on each insert, unless the application specifically disables the trigger first (unlikely).

You said there were two other triggers. What are they, and in what order to the three triggers fire?

Yes. Triggers fire regardless of the initiator of the transaction. There is nothing in that trigger that would prevent it from firing on each insert, unless the application specifically disables the trigger first (unlikely).

You said there were two other triggers. What are they, and in what order to the three triggers fire?

If I understand correctly this is a For Insert trigger? But the Inserted.Deleted = 0 would more likely be true for an Update trigger where the record is deleted first and a new record inserted. I believe rdjabarov also pointed this out.

corncrowe: yeah, it's a For Insert trigger. The Inserted.Deleted = 0 was put in to handle when new members are added via the sproc or the Access UI but are more for record keeping that they exist, but are initially "deleted". Given that the sproc doesn't insert that field, & the field defaults to 0 (false) in the table, it's likely that the trigger is getting a null, so the deleted = 0 criteria is causing the problem (I'm doing some tests on this with Access UI people now).

blindman: as far as I know, the sproc doesn't disable the trigger before doing its work, & I'm fairly certain that the Access UI doesn't either. Of course, I inherited this project well after both were developed. In terms of the order the triggers fire, I couldn't say. Is there a way to tell the DB to order them? & for completeness, below are the other 2 triggers. As everyone can see, I added the same Insert.Deleted = 0 criteria. I'm hoping that changing that will resolve the issue.

I'd consider changing the trigger to an for insert,update. The code snippet in your trigger that checks for changes can be simply a check of the update trigger and then the Inserted.Deleted != 0 would be valid.

ok, I changed the trigger as below (yes, the criteria changed from the bitUser_MonthlyOptIn flag to the email being provided). So far, this worked with the MS Access UI, & I'll be able to test the sproc by tomorrow.

Thanks for your help, & I'll let y'all know if it works in the sproc case, too.