I am going to write a Trigger to validate the column field in the table name 'LenderAddressAll' and the column name is 'Notes'.My trigger checks the data entered into that column and if it valid that's ok otherwise it'll pop up 'error'.The data format should be 000 xxxxxx.First three numbers should be 0's and then empty space and after that it should be any six digits(Total varchar(10)).
Create trigger tri
on
LenderAddressesALL
after
insert
AS
select lender.Notes from LenderAddressesALL lender join inserted i on i.Notes = lender.Notes
select lender.Notes from LenderAddressesALL lender join inserted i on i.Notes = lender.Notes
where i.Notes is NOT null
declare @temp varchar(10)
declare @temp varchar(10)
select @temp = Notes from inserted
if(len(@temp) = 10 and substring(@temp,1, 4) = '000 '
and cast(substring(@temp, 5, 10) as int) > 0
and cast(substring(@temp, 5, 10) as int) <>
return
else
begin print 'error!!!!'
RAISERROR ('Error!!', 16, 1)
rollback transaction
end
GO
GO
Happy Programming!!