Triggers
A database trigger is procedural code that is automatically executed in response to certain events on A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for
a particular table or view in a database. The trigger is mostly used for keeping the integrity of thekeeping the integrity of the information on the database. For
information on the database. For example, when a new record (example, when a new record (representing a new worker) added torepresenting a new worker) added to the employees table, new records should be c
the employees table, new records should be c reated also in the tables of reated also in the tables of the taxes, vacations, andthe taxes, vacations, and salaries.
salaries.
Triggers are commonly used to:
Triggers are commonly used to:
prevent changes (e.g. prevent an invoice from being changed after it's been mailed out)prevent changes (e.g. prevent an invoice from being changed after it's been mailed out)
log changes (e.g. keep a copy of the old data)log changes (e.g. keep a copy of the old data)
audit changes (e.g. keep a log of the users and roles involved in changes)audit changes (e.g. keep a log of the users and roles involved in changes)
enhance changes (e.g. ensure that every change enhance changes (e.g. ensure that every change to a record is to a record is time-stamped by the server'stime-stamped by the server's clock, not the client's)
clock, not the client's)
enforce business rules (e.g. require that enforce business rules (e.g. require that every invoice have at least one every invoice have at least one line item)line item)
execute business rules (e.g. notify a execute business rules (e.g. notify a manager every time an employee's bank accountmanager every time an employee's bank account number changes)
number changes)
replicate data (e.g. store a record of every change, to be shipped to another database later)replicate data (e.g. store a record of every change, to be shipped to another database later)
enhance performance (e.g. update the account balance after every detail transaction, forenhance performance (e.g. update the account balance after every detail transaction, for faster queries)
faster queries)
The major features of database triggers, and their effects, are:
The major features of database triggers, and their effects, are:
do not accept parameters or do not accept parameters or arguments (but may store affected-data in arguments (but may store affected-data in temporary tables)temporary tables)
cannot perform commit or rollback operations because they are part of the triggering SQLcannot perform commit or rollback operations because they are part of the triggering SQL statement
statement
can cancel a requested operationcan cancel a requested operation
can cause mutating table errors, if they are poorly written.can cause mutating table errors, if they are poorly written.
Microsoft SQL Server supports triggers either after or instead of an insert, update, or delete Microsoft SQL Server supports triggers either after or instead of an insert, update, or delete operation.
operation.
The syntax is as follows:
The syntax is as follows:
64
64 Creating Creating and and using using Stored Stored ProceduresProcedures
Tutorial: Database Communication in LabVIEW Tutorial: Database Communication in LabVIEW
CREATE TRIGGER
CREATE TRIGGER <TriggerName> <TriggerName> on on <TableName><TableName>
FOR INSERT, UPDATE, DELETE FOR INSERT, UPDATE, DELETE AS
AS
… Create your Code here
… Create your Code here GO
GO
Replace <TriggerName> with the Name of your TriggerReplace <TriggerName> with the Name of your Trigger
Replace <TableName> with the Name of your Replace <TableName> with the Name of your TableTable Define when the Trigger should be
Define when the Trigger should be executeexecute
If the Trigger should be executed only when you insert data into the table:If the Trigger should be executed only when you insert data into the table: FOR INSERTFOR INSERT
If the Trigger should be executed only when you update data into the table:If the Trigger should be executed only when you update data into the table: FOR UPDATEFOR UPDATE
If the Trigger should be executed only when you delete data into the table:If the Trigger should be executed only when you delete data into the table: FOR DELETEFOR DELETE
If the Trigger should be executed when you insert and update data into the table:If the Trigger should be executed when you insert and update data into the table: FORFOR INSERT, UPDATE
The Example above change the “below” in
The Example above change the “below” in the Table “SCHOOL” from ‘TUC’ to the Table “SCHOOL” from ‘TUC’ to ‘Telemark University‘Telemark University College’
College’
CREATE
CREATE TRIGGERTRIGGER CheckSchoolData CheckSchoolData onon SCHOOL SCHOOL FOR
FOR INSERTINSERT,, UPDATEUPDATE AS
AS
DECLARE DECLARE
@SchoolName
@SchoolName varcharvarchar((5050))
select
select @SchoolName @SchoolName==SchoolNameSchoolName fromfrom INSERTED INSERTED If
If @SchoolName @SchoolName=='TUC''TUC' update
update SCHOOL SCHOOL setset SchoolName SchoolName=='Telemark University College''Telemark University College' wherewhere SchoolName
SchoolName==@SchoolName@SchoolName GO
GO
Note!
Note!Note the use of a temporary table called “Note the use of a temporary table called “INSERTEDINSERTED”. This temporary table contains the l”. This temporary table contains the lastast inserted record into the SCHOOL table
inserted record into the SCHOOL table Note!
Note! In SQL you define a variable like this In SQL you define a variable like this DECLARE
65
65 Creating Creating and and using using Stored Stored ProceduresProcedures
Tutorial: Database Communication in LabVIEW Tutorial: Database Communication in LabVIEW
Note!
Note! You have to use the symbol “You have to use the symbol “ @@” before the name of the variable!!!” before the name of the variable!!!
Below we see how we create a Trigger from the “
Below we see how we create a Trigger from the “SQL Server Management StudioSQL Server Management Studio”:”:
Check if the Trigger is working as expected:
Check if the Trigger is working as expected:
Procedure:
Procedure:
Step 1: Check the data in your table before you do anything, e.g.:
Step 1: Check the data in your table before you do anything, e.g.:
select
select ** from SCHOOL from SCHOOL
Step 2: Insert some test data into your table, e.g.:
Step 2: Insert some test data into your table, e.g.:
insert
insert intointo SCHOOL SCHOOL ((SchoolIdSchoolId,, SchoolName SchoolName)) valuesvalues ((55,, 'TUC''TUC')) Step 3: Check the data has been updated according to your code in the Trigger:
Step 3: Check the data has been updated according to your code in the Trigger:
select
select ** from SCHOOL from SCHOOL
66
66 Creating Creating and and using using Stored Stored ProceduresProcedures
Tutorial: Database Communication in LabVIEW Tutorial: Database Communication in LabVIEW
→ As you see the data you inserted into the table has been automatically been changed by the
→ As you see the data you inserted into the table has been automatically been changed by the Trigger
Trigger
13.1
13.1 Exercises Exercises
Create a Trigger that adds “+47” to all Phone numbers in the CUSTOMER table.
Create a Trigger that adds “+47” to all Phone numbers in the CUSTOMER table.
Test and see if the Trigger works properly by inserting and updating some data in the CUSTOMER Test and see if the Trigger works properly by inserting and updating some data in the CUSTOMER table.
table.
67 67