User-defined functions are somewhat similar to stored procedures. The big difference is that user defined functions can be invoked within SQL statements.
Creating User·Defined Functions
A CLR user-defined function can be defined with the attribute [SqlFunction], The sample function Hash () converts the string that is passed to a hashed string.
The MD5 algorithm that is used for hashing the string is implemented with the class MD5 Crypto Service Provider from the namespace System. Security.
Cryptography The ComputeHash ( ) method computes the hash from the byte
array input and returns a computed hash byte array. The hashed byte array is converted back to a string by us.i.n.g the StringBuilder class:
Using User-Defined Functions
A user-defined function can be deployed with SQL Server very similarly to the other .NET extensions: either with VISual Studio 2008 or with the CREATE FUNCTIONstatement:
A sample usage of the CalcHash () function is shown with this SELECT statement where the credit card number is accessed from the Credi tCard table in the AdventureWorks database by returning just the hash code from the credit card number: – SELECT Sales.CreditCard.CardType AS [Cd Type],
dbo.CalcHash(Sales.CreditCard.CardNumber) AS [Hashed Card]
FROMSales.CreditCard INNER JOIN Sales.ContactCreditCard ON
Sales Credi tCard. Credi tCardIO = Sales. ContactCredi tCard. Credi tCardID
WHERESales.ContactCreditCard.ContactIO = 11
The result returned shows the hashed credit card number for contact ID 11:
Card Type Hashed Card
A trigger is a specttI kind of stored procedure invoked when a table is modified (for example, when row is inserted, updated, or deleted). Triggers are associated with tables and the action that should activate them (for example, on insert/update/delete of rows).
With triggers, changes of rows can be cascaded through related tables or more complex data integrity can be enforced.
Within a trigger you have access to the current data of a row and the original data, so it is possible to reset the change to the earlier state, Triggers are automatically associated with the same transaction the command that fires the trigger, so you get a correct transactional behavior.
The trigger uCreditCard that follows is part of the Adventure Works sample database, This trigger fired when a row in the CreditCard table is updated, With this trigger the modifiedDate column of the CreditCard table is updated to the current date, For accessing the data that is changed, the temporary table inserted is used.
CREATETRIGGER [Sales]. [uCreditCard] ON [Sales]. [CreditCard]
AFTER UPDATENOT FOR REPLICATION AS
SET NOCOUNTON; •
UPDATE [Sales]. [CreditCard]
SET [Sales]. (CreditCard]. [ModifiedOate] = GETOATE()
WHEREinserted. [CreditCardIO] = [Sales]. [CreditCard]. [CreditCardIO];
The example shown here demonstrates implementing data integrity with triggers when new records are inserted into the Users table. To create a trigger with the CLR. a simple class must be defined that includes static methods that have the attribute [Sql Trigger] applied. The attribute fllql Trigger J defines the table that is associated with the trigger and the event when the trigger should occur. In the
example, the associated table is Person. Contact, which is indicated by the Target property.
The Event property defines when the trigger should occur; here, the event string is set to FOR-INSERT, which means the trigger is started when a new row is inserted in the Users table, The property SqlContext TriggerContext returns the trigger context in an object of type SqlTriggerContext, The SqlTriggerContext class offers three properties: ColumnsUpdated returns a Boolean array to flag every column that was changed, EventData contains the new and the original data of an update in XMLformat, and TriggerAction returns an enumeration of type Triggerlction to mark the reason for the trigger, The example compares whether the TriggerAction of the trigger context is set to TriggerAction. Insert before continuing.
Triggers can access temporary tables; for example, in the following code listing the INSERTED table is accessed, With INSERT,UPDATE, and DELETESQLstatements, temporary tables are created, The INSERTstatement creates an INSERTEDtable; the DELETEstatement creates a DELETEDtable. With the UPDATEstatement both INSERTEDand DELETEDtables are used. The temporary tables have the same columns as the table that is associated with the trigger. The SQLstatement SELECTUsername, Email FROMINSERTEDis used to access usemame and email, and to check the email address for correct syntax, SqlCommand.Execu teRow () returns a row represented in a SqlDa taRecord. User name and email are read from the data record. Using the regular expression class, RegEx, the expression used with the Is Match () method checks if the email address conforms to valid email syntax. If it does not conform, an exception is thrown and the record is not inserted, because a rollback occurs with the transaction:
Using deployment of VISualStudio 2008, the trigger can be deployed to the database. You can use the CREATE TRIGGER command to create the trigger manually:
CREATE TRIGGER InsertContact ON Person. Contact
AS EXTERNAL NAME Demo.Triggers.InsertContact
Trying to insert rows into the Users table with an incorrect email throws an exception, and the insert is not done.