Triggers are implemented at the database layer and fired off by the database engine itself. This means you do not need to modify the code within your front-end application that the users see.
This is particularly important and beneficial when the program code is not in your control anyhow, like a third-party application.
It is also of value when you have multiple interfaces to your data - perhaps via a web app, a fat-client app and a mobile app - whether you can maintain the source code or not. In this case, the trigger can be created within the database and applies no matter how the user is working.
There are, of course, some catches.
I'll describe these on page two, and then give working T-SQL trigger code on page three to illustrate.
Additionally, you must consider possible performance hits. A trigger will be activated each and every time something is changed in the specific database table you are targeting. If that table is frequently written to or updated then you are firing many, many triggers, each with its own small grab at the CPU.
If you wish an alert to be raised immediately when certain database changes occur then a trigger is for you; however, if you only want (say) a daily report indicating what has changed then there are other means of achieving this which can be implemented to execute out of working hours.
Over the page is a simple trigger implemented in Microsoft's SQL Server T-SQL language. The principles are identical for other enterprise databases though the syntax will vary.
In this example, we wish to be notified whenever a new record is inserted into the PurchaseOrders table, or whenever an existing record is modified.
We create a trigger using the T-SQL 'create trigger' command, specifying both the table to monitor (PurchaseOrders) and the actions that cause it to fire (insert, update).
Within the program code we have access to a special table called inserted - this can be queried to divulge precisely what the new or changed data is. Similarly, a trigger that fires whenever data is deleted has access to a special table called deleted.
Note that in the below code it is assumed that only one row is modified at a time, which is typically the case when operating an application. However, if an administrator performed a bulk UPDATE command then the trigger would break because each of the SELECTs from table inserted would return multiple values.
You will need to test your trigger against thorough test cases to ensure it is not going to break in production, but despite this the power it offers in instantaneous alerting, plus auditing of modifications, is unparalleled.
create trigger SendNotification declare @PONumber nchar(10) set @PONumber = (select PONumber from inserted) declare @Msg varchar(500) set @Msg = 'PO number: ' + @PONumber + char(13) + exec msdb.dbo.sp_send_dbmail
after insert, update
declare @User varchar(250)
declare @Division varchar(50)
declare @Location varchar(50)
declare @Notes varchar(250)
set @User = (select [User] from inserted)
set @Division = (select Division from inserted)
set @Location = (select [Location] from inserted)
set @Notes = (select [Notes] from inserted)
if @Notes is NULL
set @Notes = ''
'User: ' + @User + char(13) +
'Division: ' + @Division + char(13) +
'Location: ' + @Location + char(13) +
'Notes: ' + @Notes
@recipients = 'firstname.lastname@example.org',
@body = @Msg,
@subject = 'Purchase order change';
declare @PONumber nchar(10)
set @PONumber = (select PONumber from inserted)
declare @Msg varchar(500)
set @Msg = 'PO number: ' + @PONumber + char(13) +