update trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

update trigger

Author

Topic

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-08 : 05:24:40

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TRIGTEMP1]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TRIGTEMP1]
GO

CREATE TRIGGER [TRIGTEMP1] ON [dbo].[employee]
FOR UPDATE
AS
declare @a int, @b int, @C int, @d varchar(200), @E int
SELECT @c = COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE'
set @a = 1
while @a < @c + 1
BEGIN
set @b = convert(int,SUBSTRING(COLUMNS_UPDATED(),@a,1))
IF @b > 1
begin
if (@b & 1 = 1)
begin
SET @e = (@a - 1) * 8 + 1

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 2 = 2)
begin
SET @e = (@a - 1) * 8 + 2

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 4 = 4)
begin
SET @e = (@a - 1) * 8 + 3

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 8 = 8)
begin
SET @e = (@a - 1) * 8 + 4

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 16 = 16)
begin
SET @e = (@a - 1) * 8 + 5

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 32 = 32)
begin
SET @e = (@a - 1) * 8 + 6

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 64 = 64)
begin
SET @e = (@a - 1) * 8 + 7

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 128 = 128)
begin
SET @e = (@a - 1) * 8 + 8

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end
end

set @a = @a + 1
end

Kapil Arya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-08 : 05:37:08

Kapil, It would be nice if you give some description about your script

Madhivanan

Failing to plan is Planning to fail

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-08 : 06:40:08

Please read the below theory abt the COLUMNS_UPDATED ( )

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}

IF (COLUMNS_UPDATED())

Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern that indicates which columns in the table were inserted or updated.

The COLUMNS_UPDATED function returns the bits in order from left to right, with the least significant bit being the leftmost. The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost. COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

COLUMNS_UPDATED can be used anywhere inside the body of the trigger.

bitwise_operator

Is the bitwise operator to use in the comparison.

updated_bitmask

Is the integer bitmask of those columns actually updated or inserted. For example, table t1 contains columns C1, C2, C3, C4, and C5. To check whether columns C2, C3, and C4 are all updated (with table t1 having an UPDATE trigger), specify a value of 14. To check whether only column C2 is updated, specify a value of 2.

comparison_operator

Is the comparison operator. Use the equal sign (=) to check whether all columns specified in updated_bitmask are actually updated. Use the greater than symbol (>) to check whether any or some of the columns specified in updated_bitmask are updated.

column_bitmask

Is the integer bitmask of those columns to check whether they are updated or inserted.

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

Kapil Arya

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-08 : 06:44:47

every 8 columns update information comes in a binary form

SUBSTRING(COLUMNS_UPDATED(),@a,1)

and after doing bitwise operator with 1,2,4,8,16, or u can say (2)^(n-1) if we get the same (2)^(n-1) then that column no is modified, and "from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e'" will give the column name.

so this script gives which all columns of the table are modified.

here i have used employee table for making the script.

Kapil Arya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources