If you have your database in 65 compatibility mode, you need to be extra careful, if you add an additional trigger to a table.
Let us take this example,
you have a database named test.
First move the database to 65 compatibility mode with the help of following statement.
sp_dbcmptlevel test,65
Then create a table and name it as working_table.Now create a trigger for insert action on the table with name as working_table_insert_trigger1.
If you check now the number of triggers on the table with sp_helptrigger working_table, the sql server will show the newly created trigger working_table_insert_trigger1.
And here is the twist, if you wish to add an additional trigger on the table working_Table for the same insert activity, you need to be careful.
let us assume, you proceed normally and create a trigger in the following way,
Create trigger working_table_insert_trigger2
on working_table
for insert
as
begin
select 1
end
If you check the sqlserver, you first trigger will be lost.
sp_helptrigger working_table will return only the latest trigger.The embarrassing thing is you wont get any warning message when you create the second trigger.
You have to use the append clause if you want to create multiple triggers on a table that is in a database with 65 compatibility mode.
Create trigger working_table_insert_trigger2
on working_table
for insert
with append
as
begin
select 1
end
If you missed the "with append" option it means you are going to lose all your code in the previous triggers.
Another thing that Microsoft disappoints is, when We modify the triggers that we added later with "with append" option.
You should not use this "with append" option when you alter the trigger. This article may be on an outdate topic but still worth reading and useful.
