Trigger when selected columns is updated

In this topic, i will discuss how to execute a trigger in one table when a certain columns was updated.

I assumption we have a Table (TBL_STUDENT) with 7 columns in it, the following columns name is : ID, Name, Address, Gender, DoB, Religion, Country And Table (TBL_LOG) with 5 columns in it, the following columns name is : LogTime, LogType, LogUser, ID, Name, Gender. TBL_LOG is used to save all the update that happen in TBL_STUDENT

Now i want to create a trigger in table TBL_STUDENT that will execute when columns Name And Gender was updated.

Here is the following Syntax from create table until create trigger:

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘TBL_STUDENT’)
DROP TABLE TBL_STUDENT
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘TBL_LOG’)
DROP TABLE TBL_LOG;
GO

CREATE TABLE TBL_STUDENT (
ID VARCHAR(5) NOT NULL,
NAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(100),
GENDER VARCHAR(6),
DOB    DATETIME,
RELIGION VARCHAR(10),
COUNTRY VARCHAR(20),
PRIMARY KEY(ID)
)

CREATE TABLE TBL_LOG (
LOGTIME DATETIME DEFAULT GETDATE(),
LOGTYPE VARCHAR(6),
LOGUSER SYSNAME DEFAULT SUSER_NAME(),
ID VARCHAR(5),
NAME VARCHAR(50),
GENDER VARCHAR(6)
)
GO

CREATE TRIGGER updStudent
ON TBL_STUDENT
AFTER UPDATE AS
IF (COLUMNS_UPDATED() & 10) > 0
BEGIN
–FOR OLD RECORD
INSERT INTO TBL_LOG(LOGTYPE,ID,NAME,GENDER)
SELECT ‘OLD’,old.ID,old.NAME,old.GENDER FROM DELETED old
–FOR NEW RECORD
INSERT INTO TBL_LOG(LOGTYPE,ID,NAME,GENDER)
SELECT ‘NEW’,new.ID,new.NAME,new.GENDER FROM INSERTED new
END
GO

that happen to be question is maybe the following syntax

IF (COLUMNS_UPDATED() & 10) > 0

COLUMNS_UPDATED() –> is a syntax that will generated which columns that had been updated either it is column 1, 2, 3 & etc..
10 –> is the number that see whether all column 2 (Name) & 4(Gender) are updated.

The question is how we can generate that number 10?
Here’s the trick:
To generate that 10 is by using power (^), at our example we want to execute trigger only if column 2 & 4 was updated.

so we can use this calculation to generate the number:
2^(<column number>-1)+2^(<column number>-1)+….+2^(<ncolumn number>-1) = x
2^(2-1)+2^(4-1)=x
2^(1)+2^(3)=x
2 + 8 = x
10 = x
So 10 is used to check whether column 2 or 4 was updated or not.

Hope This Usefull,

Best Regard,
Reagan

About evocruzader

I am a honest, energetic, cool, n fun... gather near me and u will live with full of joy n fun. ^^
This entry was posted in Coding, IT and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s