Combine INSERTED and DELETED in a trigger without using JOIN

Question

I have an audit solution that uploads the contents of an INSERTED or DELETED table into a trigger for XML along with the current user, timestamp, etc. For inserts and updates, it registers the first, and for its deletion, it registers the last.

However, in order to determine what has changed between the two logs for the same record, I need to join the audit table myself to get the previous record. This alone does not work too badly, but performance will be greatly improved if I can register both data_from and data_to in the trigger.

The obvious solution is to use the inner join between INSERTED and DELETED for updates, but the problem is that these tables are not indexed and cannot be indexed, so for the database you need to execute a full row-line hash in order to get the result. Since the order in the INSERTED and DELETED records is identical in the update trigger, I cannot help but feel that it should be somehow, I can combine these two tables horizontally without using a join and without using a cursor.

What I have already tried and what I know will not work:

• Using ROW_NUMBER in a common table expression will not work - CTE is not indexed
• Inserting the contents of INSERTED and DELETED into temporary tables, indexing those, and then using them in the join is one backup option that I have if I cannot find a better solution.
• Using two cursors, one for INSERTED and one for DELETED - this is out of the question.
• Joining the audit trail table in a trigger to get the previous XML also works, but not as good as the 2 above, and since I have the data I need in the DELETED table, I can't help but feel that there should be something- then useful, I can handle it.

Any thoughts?

Answer 1

Do you consider using the "Replace Data" feature? It captures changes more efficiently than a trigger, and it is an asynchronous background process, which means minimal impact on the processes that make updates.

Answer 2

In the end, I decided to transfer the data in the inserted and deleted tables to the indexed table variables, and then work with them. Performance is not as good as it would be with CDC, but acceptable and linear, and time to market was much shorter. I wrote a code generator for generating triggers, a sample of which I included below:

 IF TRIGGER_NESTLEVEL(OBJECT_ID('TR_su_type_code_audit_log')) > 1 RETURN DECLARE @user_key INT, @tp INT = 0 IF EXISTS(SELECT 1 FROM deleted) SET @tp += 1 IF EXISTS(SELECT 1 FROM inserted) SET @tp += 2 DECLARE @i TABLE (type_code_key int, audit_data VARCHAR(MAX), PRIMARY KEY (type_code_key)) DECLARE @d TABLE (type_code_key int, audit_data VARCHAR(MAX), PRIMARY KEY (type_code_key)) INSERT INTO @i SELECT type_code_key, (SELECT type_code_key, type_code_group, id, description, is_system_reserved, site_key, code_int, code_str, image_index, image_filename FOR XML RAW('audit')) FROM inserted INSERT INTO @d SELECT type_code_key, (SELECT type_code_key, type_code_group, id, description, is_system_reserved, site_key, code_int, code_str, image_index, image_filename FOR XML RAW('audit')) FROM deleted SET @user_key = dbo.f_get_current_user() IF @tp = 2 BEGIN INSERT INTO audit_trail (mod_type, mod_date, user_key, audit_rec_table, audit_rec_key, audit_rec_key_1, audit_rec_key_2, is_delta, data_to) SELECT 'I', GETDATE(), @user_key, 'su_type_code', t.type_code_key, NULL, NULL, 0, audit_data FROM @it END ELSE IF @tp = 1 BEGIN INSERT INTO audit_trail (mod_type, mod_date, user_key, audit_rec_table, audit_rec_key, audit_rec_key_1, audit_rec_key_2, is_delta, data_from) SELECT 'D', GETDATE(), @user_key, 'su_type_code', t.type_code_key, NULL, NULL, 0, audit_data FROM @dt END ELSE BEGIN INSERT INTO audit_trail (mod_type, mod_date, user_key, audit_rec_table, audit_rec_key, audit_rec_key_1, audit_rec_key_2, is_delta, data_to, data_from) SELECT 'U', GETDATE(), @user_key, 'su_type_code', t.type_code_key, NULL, NULL, 0, t.audit_data, d.audit_data FROM @it INNER JOIN @dd ON (t.type_code_key = d.type_code_key) WHERE ISNULL(t.audit_data, '') <> ISNULL(d.audit_data, '') END SET NOCOUNT OFF; 

Answer 3

My working solution is to place a unique column in a table, such as IDENTITY. Then attach DELETED and INSERTED only to this unique column.