|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > COLUMNS_UPDATED() and SQL7
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
COLUMNS_UPDATED() and SQL7
|
|
|
| I am attempting to create a trigger that logs to tblB any
changes to from 1 to 50 fields in tblA.
The updated_bitmask is pretty large, > 5.6E14. I'm using
the following:
"IF (COLUMNS_UPDATED() & {bitmask}) > 0"
I keep getting the following error:
"Invalid operator for data type. Operator equals boolean
AND, type equals numeric."
Can someone point me in the right direction???
Thanks,
P
| |
| Dejan Sarka 2002-10-05, 9:12 pm |
| Paul,
Columns_Updated can be used in simply manner to tost just for first 8
columns. For more, you need a little bit more complicated procedure. This is
from 2000 BOL, I don't have 7.0 at the moment, hope it will help you anyway:
-------------------
F. Use COLUMNS_UPDATED to test more than 8 columns
If you must test for updates that affect columns other than the first 8
columns in a table, you must use the SUBSTRING function to test the proper
bit returned by COLUMNS_UPDATED. This example tests for updates that affect
columns 3, 5, or 9 in the Northwind.dbo.Customers table.
USE Northwind
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1
,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2
,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GO
UPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Paul" <pward@metrotelco.com> wrote in message
news:482901c260db$5857d250$3be
f2ecf@TKMSFTNGXA10...
> I am attempting to create a trigger that logs to tblB any
> changes to from 1 to 50 fields in tblA.
>
> The updated_bitmask is pretty large, > 5.6E14. I'm using
> the following:
>
> "IF (COLUMNS_UPDATED() & {bitmask}) > 0"
>
> I keep getting the following error:
>
> "Invalid operator for data type. Operator equals boolean
> AND, type equals numeric."
>
> Can someone point me in the right direction???
>
> Thanks,
>
> P
|
|
|
|
|