Real Scenario Logic's

TRIGGERS+DYNAMIC QUERY to avoid HUGE Existing code changes

Recently came across a below question.

Requirement is to trim leading and trailing spaces for a particular column name when ever there is insert or update happens to the tables having that particular column name.
The User would like to do this in real time when ever the data is inserted or updated to the table.
This column was available in some 20 to 30 tables.Is there any method available? we can not prevent source which is inserting the data as there is lot of applications involved and huge code change is required and the User not wants to do changes in existing codes.

For the Above Question, the known choices came to mind are Staging Tables/Temporary Tables/Sub Queries/Table Variables/LTRIM(RTRIM(particular column)) everywhere to make the trim changes for that particular column before those data proceeding to main tables. The disadvantage while using those are, it needs huge code changes in existing flows/stored procs/codes. User not want to do changes in existing codes.

Another option is using Computed Column for all those particular column in all respective table Schema. The disadvantage while using those are, it needs more Logical DDL script changes and handlings / code changes in existing flows/column name changes in stored procs/codes. User not want to do changes in existing codes.

Another option is using TRIGGER and the main advantage of it is to avoid code changes in existing code. User Requirement is Matched. But the second task is creating Scripts for all tables having that column.To Simplify this, we can use Dynamic Query as one time that would generate scripts automatically.So that the user can execute it at single shot.

Sample DDL scripts for Scenario:

-- SAMPLE DDL SCRIPTS FOR SCENARIO
/*
IF OBJECT_ID('product') IS NOT NULL
	DROP TABLE product;
IF OBJECT_ID('products') IS NOT NULL
	DROP TABLE product1;
*/

--SET NOCOUNT ON;

CREATE TABLE product (item VARCHAR(100))
GO
Create table products (item VARCHAR(100))
GO

Sample INSERT scripts for Scenario:

--SAMPLE INSERT SCRIPTS FOR SCENARIO
INSERT INTO product(item)
VALUES (' BAT '),('BALL '),(' STUMP'),('SHIRT');
GO
INSERT INTO products(item)
VALUES (' BAT '),('BALL '),(' STUMP'),('SHIRT');
GO

SELECT 'product' AS [product],* FROM product;
GO
SELECT 'products' AS [products],* FROM products;
GO

Sample Auto Generating Scripts of CREATE TRIGGER Dynamically:

--USE BELOW SCRIPT FOR GENERATING SCRIPTS FOR REQUIRED COLUMN DYNAMICALLY
DECLARE @Column_name NVARCHAR(500)='Item'; --GIVE YOUR COLUMN_NAME HERE
IF OBJECT_ID('TEMPDB..#TEMPS') IS NOT NULL
	DROP TABLE #TEMPS;
SELECT * INTO #TEMPS FROM 
(
SELECT CAST('CREATE OR ALTER TRIGGER ' + QUOTENAME(TABLE_SCHEMA) + '.[Trigger_' + REPLACE(QUOTENAME(TABLE_NAME),'[','') + ' ON ' + 
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' AFTER INSERT,UPDATE AS BEGIN UPDATE ' + QUOTENAME(TABLE_SCHEMA) 
+ '.' + QUOTENAME(TABLE_NAME) + ' SET ' + QUOTENAME(COLUMN_NAME) + '=LTRIM(RTRIM(' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) +
'.' + QUOTENAME(COLUMN_NAME) + '))' + ' FROM INSERTED END; ' AS NVARCHAR(MAX)) 
AS DYNAMIC_TEXT,CAST('GO' AS NVARCHAR(MAX)) AS BREAK_QUERY
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=@Column_name ) A;
SELECT DYNAMIC_Query123 FROM
(SELECT DYNAMIC_TEXT,BREAK_QUERY
        FROM #TEMPS) t
       UNPIVOT (DYNAMIC_Query123
               FOR DYNAMIC_Query IN ([DYNAMIC_TEXT],[BREAK_QUERY])
                            ) unpvt;
GO

Generated TRIGGER Script:

--SCRIPT (check and run scripts that are generated dynamically)
/*
CREATE OR ALTER TRIGGER [dbo].[Trigger_product] ON [dbo].[product] AFTER INSERT,UPDATE AS BEGIN UPDATE [dbo].[product] SET [item]=LTRIM(RTRIM([dbo].[product].[item])) FROM INSERTED END; 
GO
CREATE OR ALTER TRIGGER [dbo].[Trigger_products] ON [dbo].[products] AFTER INSERT,UPDATE AS BEGIN UPDATE [dbo].[products] SET [item]=LTRIM(RTRIM([dbo].[products].[item])) FROM INSERTED END; 
GO
*/

Finally, Scripts for Unit testing the above codes:

--CHECK BY DO SOME INSERTIONS CHECK
INSERT INTO product(item)
VALUES (' BAT1 '),('BALL1 '),(' STUMP1'),('SHIRT1');
GO
INSERT INTO products(item)
VALUES (' BAT2 '),('BALL2 '),(' STUMP2'),('SHIRT2');
GO

--CHECK,DATA ARE CORRECTED FOR BOTH OLD/NEW INSERTING DATA
SELECT 'product' AS [product],* FROM product;
GO
SELECT 'products' AS [product1],* FROM products;
GO

Thanks for reading!

Advertisement

2 thoughts on “TRIGGERS+DYNAMIC QUERY to avoid HUGE Existing code changes

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 )

Connecting to %s