There are some cases that you can not change your code if you have an ancient project. If it causes to insert wrong values to db, you may want to validate the value is ok and if not, correct it. We can use db trigger for such cases.
CREATE TRIGGER [dbo].[triggerName] ON [dbo].[TableName] FOR INSERT AS BEGIN DECLARE @Name AS varchar(5000) DECLARE @Category AS varchar(50) DECLARE @ID AS int SET @FirstName=(select TestID from inserted) SET @ID = (select ResultID from inserted) IF @Category = 'CARS' BEGIN SET @Name=(select name from inserted) IF LEN(@Name) != 50 -- Our condition is the length of the name must be 50 if not, that means it has 0s and we need to replace 0s. BEGIN -- 'Lentgth is incorrect:' + Cast(LEN(@Name) as varchar(50)) IF CHARINDEX('0',@Name)>0 -- If name contains any 0s, update those 0s with 'X' - here we update the table record BEGIN PRINT 'Includes Zero' UPDATE TableName SET Items = Replace(@Name,'0','X') WHERE ID = @ID END END END -- 'IT IS NOT CATEGORY = CARS END
Important thing here is the type of trigger FOR INSERT
This type runs after each insert operation.
Multiple arguments in SQL IF:
DECLARE @StartDate AS DATETIME DECLARE @EndDate AS DATETIME SET @StartDate = NULL SET @EndDate = NULL IF (@StartDate IS NOT NULL AND @EndDate IS NOT NULL) BEGIN Select 'This works just fine' as Msg END Else BEGIN Select 'No Lol' as Msg END