Source: Customer Requirements [Some specific tables] can [Customize Early Warning Report].
(In a specific table) When the user can define some fields to have a modification, send a message alert to the relevant user
Basics: Column_updated () is a method that can only be called in Insert or Update Trigger. This method returns a Varbinary value that stores inSerted or the record in which fields corresponding to the UPDATE trigger. Updated. A brief description of the Columns_Updated () method in the online help [Create Trigger] and [IF Update].
The company requires Trigger implementation: (Write a specific UPDATE trigger for each [specific table].) The main difficulty is that the way IF Update (Column) is not feasible. Other business implementation is no problem. Later, COLUMNS_UPDATED ( The value returned, the problem is resolved.
Here is just about the parsing and use of the value returned to columns_updated (). Do not consider that the user specified change field and inserts the record to the message table implementation process.
- Test data preparation.
IF exists (Select * from sysobjects where id = Object_id (n '[dbo]. [T_test]') and xtype = 'u') DROP TABLE T_TESTGO
CREATE Table T_Test (f_id int IDENTITY (1, 1) Primary Key, f_char Char (8) default '', f_varchar varchar (8) default '', f_nvarchar nvarchar (8) default '', f_datetime datetime default getdate (), f_int Int default 0, f_bigint Bigint Default 0, F_Decimal Decimal (18, 6) Default 0.00, F_Number Numeric (18, 6) Default 0.00, F_Float Float Default 0.00) Go
INSERT INTO T_TEST (F_CHAR) VALUES ('001') INSERT INTO T_TEST (F_CHAR) VALUES ('002') GO
- Write an UPDATE trigger
IF exists (select * from sysobjects where id = Object_id (n '[dbo]. [Tri_test_upd]') And ObjectProperty (ID, N'ISTRIGGER ') = 1) DROP TRIGGER TRI_TEST_UPDGO
Create Trigger Tri_Test_UPD on T_Test --with EncryptionFor UpdateasDeclare @irowcnt Int
Set @irowcnt = @@ rowcount
IF @irowcnt <1 return
Declare @stable varchar (128), @spkname varchar (32), @scoLname varchar (128)
Declare @icolcnt Int, @icolid int
Declare @i tinyint, @j tinyint, @Ival tinyint, @ ilog2 tinyintdeclare @ssql varchar (8000)
Set @stable = 't_test'set @spkname =' f_id '
- Search the current list of numbers SELECT @icolcnt = count (1) from syscolumns where id = Object_id (@STABLE)
- Take 8 fields as a small set @isegment = case when @icolcnt / 8 = @icolcnt / 8.0 Then @icolcnt / 8 else @icolcnt / 8 1 END - Deposit data into a temporary table Select * Into #inserted From insertedselect * into #deleted from Deleted
- Intermediate Table #Temp (F_PKVAL VARCHAR (254) NOT NULL Primary Key, F_OLDVAL VARCHAR (254))
Set @i = 0
While @i <@isegment begin if @icolcnt <9 set @ ival = columns_updated () else set @ ival = substring (columns_updated (), @i 1, 1)
- Is equal to 0, indicating that the 8 fields corresponding to the current section are not changed. If @ival = 0 begin set @i = @i 1 Continue End
While @IVal> 0 Begin Set @J = 0 set @ ilog2 = @IVAL / 2
While @ Ilog2> 0 Begin Set @J = @J 1 set @ ilog2 = @ ivog2 / 2 end
- Get column ID set @icolid = 8 * @i @J 1
- Give the Update column name to @scolname select @scolname = s.name from inserted as i, deleted as d, syscolumns as s where i.f_id = D.F_ID and s.id = Object_id (@stable) And S.colid = @icolid
Truncate Table #TEMP - Match Dynamic Statements Set @ssql = 'INSERT INTO #TEMP (f_pkval, f_oldval, f_newval)' 'SELECT Convert Convert (VARCHAR (200), I.' @spkname '),' ' Convert (VARCHAR (200), D. ' @scoLName '), ' ' Convert (varchar (200), I. ' @scoLname ') ' ' from #inserted as I, #deleted as d ' 'WHERE I.' @spkname '= D.' @spkname 'and I.' @Scolname '<> D.' @scolnameeexec (@ssql)
- Test output SELECT F_PKVAL, @scolname as f_column_name, f_oldval, f_newval from #Temp - actually uses the message processing after the message is inserted into the message table / * ..... INSERT INTO T_MESSAGE (....) SELECT To organize Content from #Temp * /
Set @ival = @IVAL - Power (2, @J) end
Set @i = @i 1 END
DROP TABLE #InsertedDrop Table #deletedDrop Table #Temp
Go
- Test data update t_test set f_datetime = getdate (), f_float = 0.0123, f_int = 1
- The UPDATE statement has been revised three columns - actual output 1.) 1 f_int 0 12 f_int 0 12.) 1 f_datetime May 15 2004 5:30 PM May 15 2004 5:31 PM2 f_datetime May 15 2004 5:30 PM May 15 2004 5:31 PM3.) 1 f_float 0 0.01232 f_float 0 0.0123- Algorithm Columns_updated () Method Returned Varbinary, a modification situation of all columns of the current trigger in the way to store 8 fields (modified states) in each sub-section Therefore, the program is looped to all fields in 8 fields .Set @ ipdated (), @i 1, 1) The program uses the above statement to convert a section into an integer. Test discovery: Make this small piece only has only one field to have a modification, @ iVal = 1 = 2 ^ (1-1); 2, @ iVal = 2 = 2 ^ (2-1); 3, @ ival = 4 = 2 ^ (3-1); 4, @ iVal = 8 = 2 ^ (4-11); 5, @ iVal = 16 = 2 ^ (5-1); 6, @ iVal = 32 = 2 ^ (6-1) ; 7, @ iVal = 64 = 2 ^ (7-1); 8, @ iVal = 128 = 2 ^ (8-1); and when and just in 1, 2 fields have modified: @IVal = 2 ^ (1-1) 2 ^ (2-1) = 3; The three fields of the 2nd, 5, 8 have modified: @IVal = 2 ^ (2-1) 2 ^ (5-1) 2 ^ (8-1) = 146; ... When 8 fields have modified: @IVal = 2 ^ (1-1) 2 ^ (2-1) ... 2 ^ (8-1 = 255; that is, the value of @ IVAL is nothing more than 2 ^ n - 1 (n> 0 and n <9, int) [and each of the combination). The number of members appeared up to once). So in turn, calculate: Press 2 ^ n to @IVAL to calculate a list of columns. At this point, complete the entire TRIGGER algorithm.
