code2002
asked on
SQL Server Trigger
Hi experts,
I have a trigger for insert, update and delete of any record in a table. Sometimes, user clicks update button even if he does not change anything. In these cases, the trigger creates new row in the audit table. The number of rows increase rapidly even when nothing is changed. So I want to insert into audit table only when something is changed. Please inform me how I can do this. I have about 40 to 50 columns in my table.
Thanks so much.
I have a trigger for insert, update and delete of any record in a table. Sometimes, user clicks update button even if he does not change anything. In these cases, the trigger creates new row in the audit table. The number of rows increase rapidly even when nothing is changed. So I want to insert into audit table only when something is changed. Please inform me how I can do this. I have about 40 to 50 columns in my table.
Thanks so much.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Use the Columns_Updated feature in the trigger.
While I've never tried it, I imagine that if all you want to check is if no values have changed you could just compare it to 0.
Another thought is in your application, don't enable the button until something has been modified. That would prevent the extra processing.
While I've never tried it, I imagine that if all you want to check is if no values have changed you could just compare it to 0.
Another thought is in your application, don't enable the button until something has been modified. That would prevent the extra processing.
ASKER
ScottPletcher,
thanks for ur reply. but will the quick script handle the null values ? for example, if one of the values is null, then '<>' will not work for it. Please let me know what you think.
szacks,
I read in Sql Server books online that Columns_Updated works for first 8 columns. Moreover its complicated as number of columns increase (like my case).
Thanks.
thanks for ur reply. but will the quick script handle the null values ? for example, if one of the values is null, then '<>' will not work for it. Please let me know what you think.
szacks,
I read in Sql Server books online that Columns_Updated works for first 8 columns. Moreover its complicated as number of columns increase (like my case).
Thanks.
You make an excellent point about the nulls. You would need to add a check for NULL, for example:
WHERE (ISNULL(i.[col1], 0) <> ISNULL([at].[col1], 0) OR ...
or:
WHERE ((i.[col1] IS NULL AND [at].[col1] IS NULL) OR i.[col1] <> [at].[col1]) OR ...
Btw, note that COLUMNS_UPDATE, or UPDATE(column), will be true if the column is referenced in the UPDATE, even if the value didn't change. For example, if:
UPDATE tablex
SET colx = colx
WHERE ...
Then:
IF UPDATE(colx)
will be true, even though the value didn't change.
WHERE (ISNULL(i.[col1], 0) <> ISNULL([at].[col1], 0) OR ...
or:
WHERE ((i.[col1] IS NULL AND [at].[col1] IS NULL) OR i.[col1] <> [at].[col1]) OR ...
Btw, note that COLUMNS_UPDATE, or UPDATE(column), will be true if the column is referenced in the UPDATE, even if the value didn't change. For example, if:
UPDATE tablex
SET colx = colx
WHERE ...
Then:
IF UPDATE(colx)
will be true, even though the value didn't change.
ASKER
ScottPletcher,
sorry for the delay. Thanks for your help. I gave Grade A to you :) .
sorry for the delay. Thanks for your help. I gave Grade A to you :) .
INSERT INTO auditTable
SELECT ...
FROM inserted.i
INNER JOIN auditTable [at] ON i.key = [at].key
WHERE i.col1 <> [at].col1 OR i.col2 <> [at].col2 OR ...