Stored Proc for Trigger using XML

by borigirla 20. September 2012 15:18

Hi all,
 
I’ve been given a task of modifying a script that works with triggers, preferably using XML schema which I have absolutely no experience with and I’m completely lost. Any and all help would be greatly appreciated!  What we are trying to do is modify the script so that instead of inserting a record for each column that is modified, it inserts a record grouping all columns that are modified in one field. That way we won’t insert so many records into the audit table. Again, any and all help would be greatly appreciated as I am completely lost.
 
Here are the examples my supervisor provided
 
Examples line by line (comma delimited): 
"Field Name", "Old Value", "New Value" (line feed here) 
"Field Name", "Old Value", "New Value" (line feed here) 
"Field Name", "Old Value", "New Value" (line feed here) 


Example in XML: 
SELECT ( SELECT 
'1' AS OldValue, 
'2' AS Newvalue 
FOR 
XML PATH('Field1'), 
TYPE) 
, 
( SELECT 
'x' AS OldValue, 
'y' AS NewValue 
FOR 
XML PATH('Field2'), 
TYPE) 

FOR XML PATH(''), 
ROOT('Changes') 
GO 

--Output 
<Changes> 
  <Field1> 
    <OldValue>1</OldValue> 
    <Newvalue>2</Newvalue> 
  </Field1> 
  <Field2> 
    <OldValue>x</OldValue> 
    <NewValue>y</NewValue> 
  </Field2> 
</Changes> 

 
Below is the current script. Thank you!!!
 

 
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')
CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1),
TableName varchar(128),
PrimaryKeyField varchar(1000),
PrimaryKeyValue varchar(1000),
FieldName varchar(128),
OldValue varchar(1000),
NewValue varchar(1000),
UpdateDate datetime DEFAULT (GetDate()),
UserName varchar(128)
)
GO
 
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
 
SELECT @TABLE_NAME= MIN(table_Schema + '.' +TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
and TABLE_NAME in ('tblAddress','tblAnswers','tblCase','tblCensus','tblEvidence','tblGpsSync','tblIntervention','tblMeterBattery','tblPhoto','tblRecord','tblTx')
and TABLE_SCHEMA = 'Data'
 
WHILE @TABLE_NAME IS NOT NULL
 BEGIN
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql =
'
create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
as
 
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
 
select @TableName = ''' + @TABLE_NAME+ '''
 
-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
 
-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''
 
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
 
-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_SCHEMA + ''.'' + pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_SCHEMA + ''.'' + pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_SCHEMA + ''.'' + pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end
 
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA + ''.'' + TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA + ''.'' + TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA + ''.'' + TABLE_NAME = @TableName and ORDINAL_POSITION = @field 
select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' + @fieldname + ''''''''
select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
select @sql = @sql + '','''''' + @UpdateDate + ''''''''
select @sql = @sql + '','''''' + @UserName + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname
select @sql = @sql + '' or (i.'' + @fieldname + '' is null and d.'' + @fieldname + '' is not null)''
select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and d.'' + @fieldname + '' is null)''
exec (@sql)
end
end
'
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(table_Schema + '.' +TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE table_Schema + '.' + TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME != 'sysdiagrams'
AND TABLE_NAME != 'Audit'
and TABLE_NAME in ('tblAddress','tblAnswers','tblCase','tblCensus','tblEvidence','tblGpsSync','tblIntervention','tblMeterBattery','tblPhoto','tblRecord','tblTx')
and TABLE_SCHEMA = 'Data'
END

Tags: , , ,

SQL Server Scripts

Remove Non Alphanumeric Characters

by Amol 20. July 2012 08:45

SQL Query to remove non alphanumeric characters

CREATE FUNCTION dbo.RemoveNonAlphaNumericCharacters

(

      @Str nvarchar(255)

)

RETURNS int

AS

BEGIN

 

      DECLARE @Result varchar(255)

      SET @Result = 1

 

      DECLARE @nchar nvarchar(1)

      DECLARE @position int

 

      SET @position = 1

      WHILE @position <= LEN(@Str)

      BEGIN

 

            SET @nchar = SUBSTRING(@Str, @position, 1)

            IF (ASCII(@nchar) between 32 and 128)

            BEGIN

                SET @Result = 1

            END

            ELSE

            BEGIN

                SET @Result = 0

                BREAK

            END

            SET @position = @position + 1

      END

 

      RETURN @Result

END 

Tags: ,

SQL Server Scripts

IIF() in SQL 2012

by Valmik 26. June 2012 08:01
We have used IF..ELSE and CASE..END in earlier versions of SQL to perform logical conditional operations.
However, IIF() can be used as a writing conditional CASE statements in a single T-SQL. It evaluates the expression passed in the first parameter with the second parameter depending upon the evaluation of the condition and returns either TRUE or FALSE.
 
In most of the cases we use ISNULL() function, we ofthen need to handle other cases than just comparing the operand with NULL. IIF() serves the purpose of having checks based on as many expressions as you need. However, you can nest the IIF() upto 10 levels only, just like the CASE. 
 
Example:
 
DECLARE @A INT = 10
DECLARE @B INT = 8
SELECT IIF(@A > @B, 'A IS GREATER THAN B', 'B IS GREATER THAN A')
GO;
 
Result of execution of above statement: 
-------------------
A IS GREATER THAN B  
(1 row(s) affected)
 
Another Example with expressions:
 
DECLARE @MIKE_AGE INT = 30
DECLARE @CLARA_AGE INT = 24
SELECT IIF(@MIKE_AGE > @CLARA_AGE, 'MIKE IS OLDER THAN CLARA BY '+LTRIM(STR(@MIKE_AGE-@CLARA_AGE))+' YEARS', 'CLARA IS OLDER THAN MIKE BY '+LTRIM(STR(@MIKE_AGE-@CLARA_AGE))+' YEARS')
GO;
 
Result of execution of above statement:
--------------------------------------------
MIKE IS OLDER THAN CLARA BY 6 YEARS
(1 row(s) affected)

Tags:

Category

Recent Posts

Tag cloud