How to create trigger to check column in any inserted record

How can I create a trigger to watch a couple of field values in each insert that happens on my table and peform an if statement on those values then replace those values with another value if the statement is true

also, I am not sure whether to use an AFTER or INSTEAD of to accomplish this.

What I want to do is essentially:

Create trigger triggername on tablename

watch for any insert of a row in my table

If column1 = something then set colum1 to something else

Answer this question

How to create trigger to check column in any inserted record

matt01

I'd just use a For trigger - see example below

creating table & Trigger for example purposes

CREATE TABLE TESTTABLE
(ID int
,Name varchar(255)
,Type tinyint)

GO
CREATE TRIGGER TestTrigger ON TESTTable
FOR INSERT
AS
UPDATE A
SET Type = CASE WHEN a.Type = 3 THEN 5 ELSE b.Type END
FROM TestTable a JOIN INserted b
ON a.ID = b.ID

INSERT TestTable (ID,Name,type)
SELECT 1,'test1',1
UNION
SELECT 2,'test2',3
UNION
SELECT 3,'test3',5

SELECT * FROM TestTable

see how the second insert inserts value 5 instead of value 3

Hope this helps.

A
thomastwo

thanks I'll try that. I have figured out another solution that works

Create Trigger [dbo].[IT_Restrict_AreaCodes]

ON [dbo].[dialempty]

AFTER INSERT

AS

if @@rowcount = 0 return

Update d set phonenum = '1111111111'

From dialempty d join inserted i on d.ProjectID = i.ProjectID

Where Left (i.phonenum,3) IN (203,475,860,959,406,218,320,507,612,651,763,952)
How to create trigger to check column in any inserted record

Answer this question

How to create trigger to check column in any inserted record

Hot Topic

SQL Server

How to create trigger to check column in any inserted record

How to create trigger to check column in any inserted record

matt01

thomastwo