Insert Trigger

[gaus]

Does anyone know how to create a BEFORE INSERT trigger? There is absolutely nothing about it in the Help (AFTER only). I cannot believe one does not exist - that would be nuts.
Thanks,
Gary

 

[MeanGreen]

There is no BEFORE, but there is an INSTEAD trigger.

Hope this helps.

 

[gaus]

Hi,
Yes,Oracle has one too. But one of the nice things about a BEFORE insert trigger is the ability to modify data prior to inserting it. That is just one of the uses. This is what I am trying to do, replace the Null value passed on an INSERT with a calculated field from a lookup and a passed parm. Would not an INSTEAD of trigger simply 'overrule' the standard INSERT? Perhaps, I could figure out a way to use that, then do an INSERT within the trigger - I'll check it out. I'm new to SQL Server, but have already found things I prefer in Oracle, and others I prefer in SQL Server.
Thanks!
Gary

 

[MeanGreen]

No problem. If you want to change the null value you can do it in the trigger. Example:

create table test
(idno int identity(1,1),
col1 varchar(25),
col2 varchar(25),
col3 varchar(25))

create trigger tr_test on test for Insert
as
update test
set col3 = 'inserted'
from test join inserted
on test.idno = inserted.idno
where inserted.col3 is null

insert test (col1)
values ('1st input')
insert test (col1,col3)
values ('1st input','test')

select * from test

Hope this helps.