2779B Module 6 - Triggers and XML Schemas
A trigger is a stored procedure that executes when INSERT, UPDATE, DELETE statements are executed against a table.
Note: Triggers use more resources than other data integrity thingys such as constraints.
Categories
-AFTER trigger executes after an INSERT, UPDATE, DELETE statement is executed
-INSTEAD OF trigger executes instead of an INSERT, UPDATE, DELETE statement
Trigger and initiating statement are part of a single implicit transaction.
INSERT triggers
-fires when a row is inserted
-sample syntax for an AFTER trigger
Create trigger [blah] on [db.table]
AFTER INSERT AS
BEGIN
--Do stuff with the “inserted” table
END
DELETE Triggers
-fires when rows are deleted
-sample syntax for an AFTER trigger
Create trigger [blah] on [db.table]
AFTER DELETE AS
BEGIN
--Do stuff with the “deleted” table
END
UPDATE triggers
-fires when rows are updated
-sample syntax for an AFTER trigger
Create trigger [blah] on [db.table]
AFTER UPDATE AS
BEGIN
--Do stuff with the “inserted” table
END
INSTEAD OF Trigger
DELETE instead of Triggers
-fires instead of the delete
-sample syntax
Create trigger [blah] on [db.table]
INSTEAD OF DELETE AS
BEGIN
--Do stuff with the “deleted” table
END
*Mark’s slide had INSTEAD OF DELETE NOT FOR REPLICATION AS
Nested Triggers
-A trigger that fires a trigger that fires a trigger up to 32 triggers.
-you can not create nested triggers by default, you have to turn on this ability with the sp_configure systems sproc.
To check whether nested triggers
are turned on or off in SQL Server, issue the following command in Query
Analyzer:
EXEC sp_configure
'nested triggers'
If the run_value
is 0 then the nested triggers are off, if the value is 1 then they are on.
Therefore, to turn on nested
triggers, type the following command:
EXEC sp_configure
'nested triggers', 1
RECONFIGURE
To turn them off, type the
following:
EXEC sp_configure
'nested triggers', 0
RECONFIGURE
-@@NESTLEVEL global variable will tell you how deep you are.
Recursive Trigger
Example: three tables… insert a row into table1,
-an insert trigger on table1 causes an update to table2
-an update trigger on table2 inserts a row into table3
-the insert trigger in table3 inserts a record into table1
It is also possible to have a trigger that fires it’s self.
-insert a row into table1 insert/update trigger on table1 fires and updates table1 again…
Be VERY careful with recursive triggers.
-TO enable:
ALTER Database <DBNAME>
SET RECURSIVE_TRIGGERS ON
-Considerations
-Limited to 32
-difficult to control the order of table updates
-can be replaced with non recursive logic.
Mark says controlling these is like “nailing jelly to a tree”
XML Schemas
-An XML Schema defines the elements and the relationship between elements that are valid for XML documents.
XML Schema Collections
-Named collection of one or more XML schemas
-Associated with columns or variables of xml data type
-Created with the CREATE XML SCHEMA COLLECTION statement.
-You can have one or more Schemas in a collection.
XML is validated against a XML Collection not an individual schema.
-if you want your XML document to be validated against one Schema you have to create a new collection and put that ONE schema into the collection.
How to do the “deed”:
Typed XML
-Columns or variables of the xml data type that reference an XML schema collection
-Validates the contained XML against XML schemas in the associated XML schema collection.
Syntax:
declare @xmldoc xml (<schema collection name>)
To create a xml schema collection:
CREATE XML SCHEMA COLLECTION nameSchemaCollection
AS N‘xml schema jargon here’