Trigger, a hard topic. A lot of programmers like them, admins usually hate them and only a few people can really write them. In my opinion triggers should be used sparsely. If you use triggers in your apps it is extremely important to test them thoroughly. Be sure that you do not forget test scenarios where your trigger has to deal with more than a single row. Very often I see trigger implementations that do not care for those situations. Here is an example:
create trigger TR_Customers_Insert on dbo.Customers for insert
as
if ( select i.Country from inserted i )<>'Germany' begin
raiserror( 'Currently we do not allow customers from countries other then germany to be created!', 16, 10 )
rollback
end
If you test this trigger in Enterprise Manager everything is fine. The system does not accept customers who do not come from Germany. If you test your trigger with this statement the trigger will fail:
insert into Customers
select 'ABC', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test'
union all
select 'DEF', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test', 'Test'
The reason for the problem is that the trigger is not written in a way which is ready for more than one row in the pseudo-table inserted. You can change that in this way:
alter trigger TR_Customers_Insert on dbo.Customers for insert
as
if exists ( select top 1 1 from inserted i where i.Country<>'Germany' ) begin
raiserror( 'Currently we do not allow customers from countries other then germany to be created!', 16, 10 )
rollback
end
SQL 2005 even allows writing triggers in .NET languages like C# and adds the possibility to create DDL trigger (DDL = Data Definition Language, e. g. CREATE, ALTER, etc.). The following mind map gives a general overview about triggers and new functions in SQL 2005 concerning triggers:
- Mind map as PDF file
- Mind map as PNG file