Security Implications with Triggers

• Control data access by using triggers.

• Enforce procedural business logic by using triggers.

• Filter data by using triggers.

This section revisits information covered earlier in this chapter to ensure that you have properly absorbed the material. These topics include controlling data access and enforcing business logic, as well as using triggers to implement referential integrity and filtering data.

Controlling, Filtering, and Enforcing Data Access

The discussion of AFTER triggers earlier in this chapter included several examples given that showed you when and how you might want to implement triggers.

Triggers are capable of performing any action you want during their execution, and they execute based on data modification in a table. One task that can be given to triggers is controlling what modifications might be made to the data in a table. This could include restrictions like the following:

• We would like only one customer/contact record for each company to exist. When a second customer/contact is added in the form of a new customer record, an INSERT trigger can check for the existence of that company in the Customer table. If the record exists, it can take the new contact and add it to an Alternate Contact column.

• From the deletion perspective, you might allow multiple contact records to exist for each company in the Customer table. When a contact record is deleted, a DELETE trigger can confirm that this is not the last record for that company. If it is, you can have the deletion cancelled and simply remove the contact name from the record.

These are just two examples of how triggers can control what is done to the data in a table. Triggers can be used to restrict or log any and all data modifications made to a table in your database.

Enforcing Referential and Data Integrity

Referential integrity requires that every FOREIGN KEY value map to a PRIMARY KEY value. To get a full appreciation of how FOREIGN KEY constraints and referential integrity work, consult Chapter 3. Triggers can be used to provide the same services as FOREIGN KEY constraints if you make sure each related table is updated in respect to the originally updated table. For example, an UPDATE trigger can be placed on a primary table so that it automatically performs data modifications to other related tables after changes are made to the primary table. This would mean the trigger could cascade changes to all associated tables to ensure that no orphaned or irrelevant rows exist.

Data integrity involves keeping consistent and correct data stored in a database. Methods of ensuring data integrity include validating individual columns and verifying that data in one table maps to data in another table. Triggers enable you to create custom error messages for referential and data integrity functions.

Most FOREIGN KEY constraints would be used to provide referential integrity, such as preventing the deletion of referenced rows or providing cascading deletes. For example, you might want to deal with the deletion of a row in one table, but do not want any FOREIGN KEY constraints to have an effect. If you use an INSTEAD OF trigger, then your code can execute before the FOREIGN KEY constraints, but these constraints still apply. INTEAD OF triggers do not fire if cascading FOREIGN KEY constraints are on the table, which could prevent your code from executing. You want to create your own custom handler for the deletion event that will be used in place of the FOREIGN KEY constraint.

In the following example, the FOREIGN KEY constraint ”which could have prevented you from being able to delete a ship that has assigned crew ”is removed. The problem that arises with the lack of this constraint is that you can now have crew assigned to ships that have been deleted and are now non-existent. Normally the constraint would prevent the deletion of the ship, or would delete the associated crew records. Because of the requirements, neither of these events is desirable, and instead you want to simply record the unassigned crew members . To deal with this problem, you can create an AFTER trigger to handle the situation. This custom trigger tracks the deleted ship, locates the crew from the ship, and sets their ShipAssigned value to NULL .

For this to work, you have to create a table that does not use FOREIGN KEY constraints. A sample database is defined in the following:

 CREATE DATABASE CrewAssignmentDB   ON     ( NAME = 'CrewAssignmentDB_dat',       FILENAME = 'c:\program files\microsoft sql server\mssql\data\crewdb.mdf',       SIZE = 1MB,       MAXSIZE = 1MB,       FILEGROWTH = 0MB )   LOG ON     ( NAME = 'CrewAssignmentDB_log',       FILENAME = 'c:\program files\microsoft sql server\mssql\data\crewdblog.ldf',       SIZE = 1MB,       MAXSIZE = 1MB,       FILEGROWTH = 0MB ) GO USE CrewAssignmentDB GO CREATE TABLE dbo.Ship   (ShipID         smallint                   IDENTITY(1,1),                ShipName       nvarchar(20))             GO             CREATE TABLE dbo.Crew               (CrewID         smallint                   IDENTITY(1,1),                CrewName       nvarchar(20),                ShipAssigned   smallint    NULL)             GO             INSERT INTO Ship (ShipName) VALUES ('Enterprise')             INSERT INTO Ship (ShipName) VALUES ('Voyager')             INSERT INTO Ship (ShipName) VALUES ('Defiant')             GO INSERT INTO Crew (CrewName, ShipAssigned) VALUES ('Kirk',1) INSERT INTO Crew (CrewName, ShipAssigned) VALUES ('Spock',1) INSERT INTO Crew (CrewName, ShipAssigned) VALUES ('McCoy',1) INSERT INTO Crew (CrewName, ShipAssigned) VALUES ('Janeway',2) INSERT INTO Crew (CrewName, ShipAssigned) VALUES ('Seven-of-nine',2) INSERT INTO Crew (CrewName, ShipAssigned) VALUES ('Torres',2) INSERT INTO Crew (CrewName, ShipAssigned) VALUES ('Sisko',3) INSERT INTO Crew (CrewName, ShipAssigned) VALUES ('Worf',3) GO 

Now that there are no constraints to interfere with data modifications, you can create a trigger to handle deletion of ships from the database. The following trigger serves that function:

 CREATE TRIGGER ref_delete_ship   ON Ship FOR delete AS     DECLARE @row_count int     SET @row_count = @@ROWCOUNT     DECLARE @DeletedShip nvarchar(20)     IF @row_count = 1       BEGIN         UPDATE Crew           SET ShipAssigned = NULL           FROM Crew INNER JOIN deleted             ON Crew.ShipAssigned = deleted.ShipID           WHERE Crew.ShipAssigned = deleted.ShipID         SET @row_count = @@ROWCOUNT         SET @DeletedShip = (SELECT ShipName FROM deleted)         PRINT 'Ship named ' + RTRIM(@DeletedShip) + ' has been deleted.'         PRINT 'There are ' + LTRIM(@row_count) + ' new crew without a ship.'         PRINT 'The crew without ships are:'         SELECT CrewName FROM Crew WHERE ShipAssigned IS NULL       END     ELSE       BEGIN         -- Flag the multirow deletion, and let the user know it is not allowed.         RAISERROR ('You are only able to delete one ship at a time',16,1)       END 

This trigger sets all ShipAssigned fields to NULL , and then displays a list of all crew that are currently unassigned. This trigger could have also deleted the crew members that were affected or rolled back the entire transaction. This method leaves them in the database, deals with the ship deletion, and provides a custom message. The result of a ship deletion is shown in Figure 8.11.

Figure 8.11. Triggers can serve the same function as most constraints.

NOTE

Trigger Syntax The error handling in the ship/crew trigger example has been deleted for brevity in the example. This should not be deleted from your production triggers. Additionally, this trigger returns a result set, in the form of a crew list. Triggers do not return a result set, whereas this trigger creates a result set as part of its operation.

Triggers, however, aren't your best bet when it comes to enforcing data integrity. Many simpler methods, such as constraints, operate with less processor overhead. Nonetheless, triggers can be used to program business logic and heavy error trapping.

As you have seen in this section and chapter, triggers are capable of restricting and controlling what data modifications are allowed to take place in the database.