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]
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'
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.
-- '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
PRINT 'Includes Zero'
SET Items = Replace(@Name,'0','X')
WHERE ID = @ID
-- 'IT IS NOT CATEGORY = CARS
Important thing here is the type of trigger FOR INSERT
This type runs after each insert operation.
Learn more here: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql
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)
Select 'This works just fine' as Msg
Select 'No Lol' as Msg
#sql # mssql #db-trigger, #insert-trigger, #sql-trigger
It’s been long time since I worked with SQL Server last time. I have been working with Oracle and there are differences for sure. This one is simple.
- The brackets are required if you use keywords or special chars in the column names or identifiers. You could name a column [First Name] (with a space)–but then you’d need to use brackets every time you referred to that column.
- They’re handy if your columns have the same names as SQL keywords, or have spaces in them.Example:create table test ( id int, user varchar(20) )
Oh no! Incorrect syntax near the keyword ‘user’. But this:
create table test ( id int, [user] varchar(20) )
- Regardless of following a naming convention that avoids using reserved words, Microsoft does add new reserved words. Using brackets allows your code to be upgraded to a new SQL Server version, without first needing to edit Microsoft’s newly reserved words out of your client code. That editing can be a significant concern. It may cause your project to be prematurely retired….Brackets can also be useful when you want to Replace All in a script. If your batch contains a variable named @String and a column named [String], you can rename the column to [NewString], without renaming @String to @NewString.