Clog

I ran into a wonderful issue with the way MS Access handles getting the last insert id from SQL Server. The application would fail saying ‘record was deleted’ when trying to access the newly created object. I had determined that this was because of some logging triggers I had created. Removing the triggers solved the problem.

I could not see how my trigger would be affecting the insert statement, it fired after and operated on a separate table. After a bit of testing I determined that MS Access was using the @@IDENTITY variable to get the id of the newly created record. This is a poor choice. There is IDENT_CURRENT(`table_name`) as well as SCOPE_IDENTITY(), both of which would avoid the issue. @@IDENTITY is set on any insert statement for the connection, this includes any triggers. The logging trigger inserted a new record, overwriting @@IDENTITY. So then MS Access would get that id back, try to look up the record and fail. Or worse, look up the wrong record.

My lame solution to this lame problem was to set @@IDENTITY back to the original insert id. However, the only way to do this is by inserting a new record with the table’s identity column set to the original insert id.

To do this I created a new table.

CREATE TABLE insert_id_fix (id INT IDENTITY)

Then before I do my logging insert, save the value of @@IDENTITY to a local variable.

DECLARE @insert_id INT
SET @insert_id = @@IDENTITY

Then after inserting the log, insert a new record into the insert_id_fix table.

    SET IDENTITY_INSERT insert_id_fix ON
    INSERT INTO insert_id_fix (id) VALUES(@insert_id)
    SET IDENTITY_INSERT insert_id_fix OFF
    DELETE FROM insert_id_fix

Notice we have to SET IDENTITY_INSERT ON for the table to insert our value into the IDENTITY column. I also cleared the table since we don’t want to actually keep the data.

Another solution would be to change the trigger to be INSTEAD OF and process the insert yourself after doing the logging. This might be a better solution, but has its issues too.

As far as I know this is a problem with MS Access itself, not the application. I could be wrong as I didn’t write the application myself. The error occurred when accessing the newly created object, which should’ve been handled by Access. If anyone has further insight into how Access works, please comment.

While trying to write some triggers for MSSQL I stumbled upon this nice function called COLUMNS_UPDATED(). It returns a bitmask representing the columns that were modified during an update operation. It just so happened that this was exactly what I was looking for. I had a group of columns that I wanted the trigger to act upon, the rest I didn’t care about. So I went about creating a bitmask for my columns to & against COLUMNS_UPDATED(). Here is where I ran into two problems.

First, COLUMNS_UPDATED() returns a VARBINARY, so logically I also made mine a VARBINARY. Unfortunately, MSSQL does not support bitwise operations on two VARBINARY variables. One can be a VARBINARY, but the other must be an INT. This isn’t a problem if your table is 32 columns or less. Unfortunately that was not the case for the 74-column monster of a table I was working with. I searched around and it seemed like there really wasn’t a solution to this problem. So I wrote my own. It’s a stored procedure that will perform the bitwise operations, &, |, or ^ on two VARBINARY variables. It splits the VARBINARY variables into INT sized chunks and the performs the operations. I’m sure it could be more efficient, but it works.
binary_bitwise.sql

The second wonderful thing is the way that bits are organized. Withing each byte (8 bits) the least significant bit (rightmost) is the first column. So if you were interested in columns 1,3,4 you would get: 00001101. Then within your whole bitmask the most significant byte (leftmost) is the first 8 columns. So let’s say you were interested in columns 1,3,4,7,9,15,16 then you would get 01001101 11000001. Writing out the bits in order doesn’t make much sense to the human brain, notice columns 15 and 16 are in the middle. The easiest way I found is to write out 0s in byte sized groups then go through them backwards putting 1s in your columns.
Once you figure out your bitmask convert it to a hex number and you can use in the trigger. My monster table ended up with:

IF(dbo.binary_bitwise(COLUMNS_UPDATED(),0xFFF57851150404C02102,'&') != 0x0)

. Also remember to fill out all your bits. If you only have 12 columns, you still need 16 bits. Let’s say you are interested in column 2 and 11. 00000010 00000100 = 0×24.