Sql-server – Looking for a Trigger to change inserting strings

sql serversql-server-2008t-sqltrigger

I have an MSSQL Server 2008 Database.
There are some tables in my Database like below :
table1, table2, table3, ….
each table has a NVARCHAR(50) field (Name)
Now, I wanna change the strings that each sql query inserts in this field
e.g :
We have a query that wanna insert "Sample Text" in Name field.
I wanna change it to "Sample Name" with a SQL Trigger

Is it Possible with sql triggers ? if so, How? I'm naive in T-SQL
Is there a better way to do it with SQL Server ?

Best Answer

if you want to use triggers thats your call, i am just suggesting a different approach

you can use the Default Value or Binding feature of a table

e.g in your table1, you can make the default value of Name column = 'Sample Name' by below script

ALTER TABLE table1 ADD CONSTRAINT
    DF_Table1_Name DEFAULT N'Sample Name' FOR Name
GO

Look at image below Table Default value of Column

This code below adds a trigger to your table that works after insert, it updates the name added and replaces the 'x' with '*'

CREATE TRIGGER ReplacingTrigger 
ON  table1  
AFTER INSERT 
AS  
BEGIN 
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.name = table1.name
END

i suggest if you have an ID in the table to use it in the joins to find the exact record inserted to make it accurate like below

CREATE TRIGGER ReplacingTrigger 
ON  table1  
AFTER INSERT 
AS  
BEGIN 
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.id= table1.id
END
Related Question