http://blog.netnerds.net/2008/02/create-a-basic-sql-server-2005-trigger-to-send-e-mail-alerts/
For as many times as I have read about sending e-mails using SQL Server triggers, I've rarely come across actual code samples. After someone asked for a "Triggers for Dummies" example in a Facebook SQL group, I created the following example which uses a trigger to alert a manager that an expensive item has been entered into inventory.
First, if SQL Mail isn't enabled and a profile hasn't been created, we must do so.
--// First, enable SQL SMail
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go
--//Now create the mail profile. CHANGE @email_address,@display_name and @mailserver_name VALUES to support your environment
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBMailAccount',
@email_address = 'sqlserver@domain.com',
@display_name = 'SQL Server Mailer',
@mailserver_name = 'exchangeServer'
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBMailProfile'
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBMailProfile',
@account_name = 'DBMailAccount',
@sequence_number = 1 ;
Now that SQL will support sending e-mails, let's create the sample table. This is not a useful or well designed table by any means -- it's just a simple example table:
CREATE TABLE dbo.inventory (
item varchar(50),
price money
)
GO
Now that SQL mail and the table are setup, we will create a trigger that does the following:
Creates an AFTER INSERT trigger named expensiveInventoryMailer on the inventory table. This means that the trigger will be executed after the data has been entered.
Checks for items being entered that have a price of $1000 or more
If there is a match, an email is sent using the SQL Mail profile we used above.
CREATE TRIGGER expensiveInventoryMailer ON dbo.inventory AFTER INSERT AS
DECLARE @price money
DECLARE @item varchar(50)
SET @price = (SELECT price FROM inserted)
SET @item = (SELECT item FROM inserted)
IF @price >= 1000
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'manager@domain.com', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END
GO
The only way to test a trigger is to add actual data, so let's do that here:
insert into inventory (item,price) values ('Vase',100)
insert into inventory (item,price) values ('Oven',1000)
Your email should arrive very quickly. If it doesn't, check the SQL Server mail log in SQL Management Studio by running SELECT * FROM sysmail_allitems.
Have fun!
Wednesday, February 24
Database mail.
http://articles.techrepublic.com.com/5100-10878_11-6161839.html
Database Mail, a new addition to the SQL Server 2005 database engine, is as simple to use as it is useful. Destined to be the replacement for SQL Mail, Database Mail uses a Simple Mail Transfer Protocol (SMTP) server to send e-mails rather than using the MAPI accounts that SQL Mail required. This allows your organization to send e-mails with attachments, e-mail query results, attach query results, and format HTML e-mails. It also gives you the ability to set many other configuration settings without requiring you to have an Exchange Server or configuring any type of MAPI workaround.
The advantages of Database Mail Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!
Besides being totally SMTP based, Database Mail has many other advantages:
It runs outside of the database engine, so the stress placed on the database engine is minimal.
It is cluster aware and fully supported in a clustered environment.
Its profiles allow for the redundant use of SMTP servers. (I go into more detail about this later in the article.)
It allows you to send a query text as a parameter to the stored procedure, which will execute the query and send the results in the e-mail.
The messages are sent asynchronously via a Service Broker queue, so you will not have to wait for a response when sending an e-mail.
It has multiple security features for sending e-mail, such as a filter for what attachment extensions can be sent and a governor for attachment size.
Setting up and using Database Mail
A bit of planning is required before you can set up a Database Mail solution. First, you need to have an available SMTP server so that e-mails can be sent. If you do not have an available SMTP server, read Microsoft Knowledge Base article 308161 for tips on setting one up. If you are not sure whether your organization has an SMTP server, talk to your network administrator to obtain the machine name or the IP address of the server. Your network administrator may need to configure the server so that e-mails can be sent from your SQL Server machine.
In Database Mail, the Account holds information that the database engine uses to send e-mail messages. An Account holds information for only one e-mail server, such as the account name, e-mail address, reply-to e-mail address, server name or IP address, and some optional security settings.
To send a Database Mail e-mail, a Profile must be used. A Profile is set up of one or more Accounts. This Profile-Account setup is very useful for a couple of reasons. It allows you to associate more than one Account to a profile, which means that you can associate more than one e-mail server to a profile. So, when you try to send an e-mail, each Account for the profile is tried until the message is successfully sent, which is great in case one or more of your SMTP servers is unavailable. It also allows you to develop your application code for sending e-mails without worrying about changing the Profile name for different environments. You can use the same Profile name for your Development and Production environments; the only difference is that the Accounts contained in the profiles will be different.
It is time to take a look at how to set up a Database Mail account. For our example, I will assume that you are sitting in front of a development machine for which you have sysadmin access. If you are not, you will need to be a member of the DatabaseMailUserRole in the msdb database.
The following script sets up some variables that I will use throughout the example. Note that the entire script will be run in the context of the msdb database, where Database Mail objects are stored.
USE msdb
GO
DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddressVARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)
Here I am setting up our ProfileName, AccountName, STMP server name, and the name that will display in the From field in the e-mail.
SET @ProfileName = 'DBMailProfile';
SET @AccountName = 'DBMailAccount';
SET @SMTPAddress = 'mail.yoursmtpserver.com';
SET @EmailAddress = 'DBMail@yoursmtpserver.com';
SET @DisplayUser = 'The Mail Man';
The script in Listing A does some clean up work, so if I run the script again, I won't have to worry about errors.
The following section adds our Account, Profile, and Profile-Account association to the system.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;
Now that everything is set up, I will send a test e-mail.
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body= 'Test Email Body',
@subject = 'Test Email Subject',
@profile_name = @ProfileName
To see if the message was sent successfully, I can run a query on the sysmail_allitems system view.
SELECT * FROM sysmail_allitems
Database Mail, a new addition to the SQL Server 2005 database engine, is as simple to use as it is useful. Destined to be the replacement for SQL Mail, Database Mail uses a Simple Mail Transfer Protocol (SMTP) server to send e-mails rather than using the MAPI accounts that SQL Mail required. This allows your organization to send e-mails with attachments, e-mail query results, attach query results, and format HTML e-mails. It also gives you the ability to set many other configuration settings without requiring you to have an Exchange Server or configuring any type of MAPI workaround.
The advantages of Database Mail Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!
Besides being totally SMTP based, Database Mail has many other advantages:
It runs outside of the database engine, so the stress placed on the database engine is minimal.
It is cluster aware and fully supported in a clustered environment.
Its profiles allow for the redundant use of SMTP servers. (I go into more detail about this later in the article.)
It allows you to send a query text as a parameter to the stored procedure, which will execute the query and send the results in the e-mail.
The messages are sent asynchronously via a Service Broker queue, so you will not have to wait for a response when sending an e-mail.
It has multiple security features for sending e-mail, such as a filter for what attachment extensions can be sent and a governor for attachment size.
Setting up and using Database Mail
A bit of planning is required before you can set up a Database Mail solution. First, you need to have an available SMTP server so that e-mails can be sent. If you do not have an available SMTP server, read Microsoft Knowledge Base article 308161 for tips on setting one up. If you are not sure whether your organization has an SMTP server, talk to your network administrator to obtain the machine name or the IP address of the server. Your network administrator may need to configure the server so that e-mails can be sent from your SQL Server machine.
In Database Mail, the Account holds information that the database engine uses to send e-mail messages. An Account holds information for only one e-mail server, such as the account name, e-mail address, reply-to e-mail address, server name or IP address, and some optional security settings.
To send a Database Mail e-mail, a Profile must be used. A Profile is set up of one or more Accounts. This Profile-Account setup is very useful for a couple of reasons. It allows you to associate more than one Account to a profile, which means that you can associate more than one e-mail server to a profile. So, when you try to send an e-mail, each Account for the profile is tried until the message is successfully sent, which is great in case one or more of your SMTP servers is unavailable. It also allows you to develop your application code for sending e-mails without worrying about changing the Profile name for different environments. You can use the same Profile name for your Development and Production environments; the only difference is that the Accounts contained in the profiles will be different.
It is time to take a look at how to set up a Database Mail account. For our example, I will assume that you are sitting in front of a development machine for which you have sysadmin access. If you are not, you will need to be a member of the DatabaseMailUserRole in the msdb database.
The following script sets up some variables that I will use throughout the example. Note that the entire script will be run in the context of the msdb database, where Database Mail objects are stored.
USE msdb
GO
DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddressVARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)
Here I am setting up our ProfileName, AccountName, STMP server name, and the name that will display in the From field in the e-mail.
SET @ProfileName = 'DBMailProfile';
SET @AccountName = 'DBMailAccount';
SET @SMTPAddress = 'mail.yoursmtpserver.com';
SET @EmailAddress = 'DBMail@yoursmtpserver.com';
SET @DisplayUser = 'The Mail Man';
The script in Listing A does some clean up work, so if I run the script again, I won't have to worry about errors.
The following section adds our Account, Profile, and Profile-Account association to the system.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;
Now that everything is set up, I will send a test e-mail.
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body= 'Test Email Body',
@subject = 'Test Email Subject',
@profile_name = @ProfileName
To see if the message was sent successfully, I can run a query on the sysmail_allitems system view.
SELECT * FROM sysmail_allitems
Triggers in SQL
http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/
Triggers allow us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table. In this article David will describe exactly what triggers are, he will show you how to create new triggers from scratch, how to test triggers, and also provide you with some valuable links/books to help you learn more about triggers.Designing a database for a large-scale web application is a monstrous task to say the least. It can however, be made easier by taking full advantage of the many tools, utilities and built-in objects that come with a Relational Database Management System (RDBMS), such as Microsoft SQL Server 7/2000.
One of these objects that many developers overlook is the trigger. Triggers are "attached" to a table and allow us to setup our database in such a way that whenever a record is added, updated, or deleted from a table, then SQL server will automatically execute a batch of SQL code after that table modification takes place.
We can do some really powerful things with the help of triggers. In this article I will describe exactly what triggers are, show you how to create new triggers from scratch, how to test those triggers, and also provide you with some valuable links/books to learn more about triggers.
A trigger is an object contained within an SQL Server database that is used to execute a batch of SQL code whenever a specific event occurs. As the name suggests, a trigger is “fired” whenever an INSERT, UPDATE, or DELETE SQL command is executed against a specific table.
Triggers are associated with a single table, and are automatically executed internally by SQL Server. Let’s create a very basic trigger now (I am using Microsoft SQL Server 7.0 on a Windows 2000 machine).
Start by opening Enterprise Manager (Start -> Programs -> Microsoft SQL Server 7.0 -> Enterprise Manager). In this example we will create our trigger against the “authors” table of the "pubs" database, so drill down through the tree view in the left pane until you can see the "“authors" table of the "pubs" database in the right pane, like this:
Next, right click on the "authors" table and choose All Tasks -> Manage Triggers... this will open the trigger properties window, which allows us to create a new trigger:
Delete all the text in the text box; we won’t need it because we’re creating our trigger from absolute scratch. All triggers are created using the "CREATE TRIGGER" command. The syntax of the “"REATE TRIGGER" command is shown below:
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
It looks really confusing, but it’s actually quite simple. I won’t go into detail about it, but you can visit http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp for the full explanation. Enter the following SQL code into the text box:
CREATE TRIGGER trig_addAuthor
ON authors
FOR INSERT
AS
-- Get the first and last name of new author
DECLARE @newName VARCHAR(100)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)
-- Print the name of the new author
PRINT 'New author "' + @newName + '" added.'
Click on the "OK" button. We have just created a new trigger named "trig_addAuthor", which is attached to the "authors" table of the "pubs" database. Whenever a new record is added to the "authors" table, SQL Server will automatically execute our trigger. Let’s discuss the actual SQL code that makes up the trigger:
CREATE TRIGGER trig_addAuthor
ON authors
These two lines tell SQL server that we want to create a new trigger object named "trig_addAuthor", which will be attached to the "authors" table.
FOR INSERT
Here, we have specified that our trigger will be executed whenever an "INSERT" command is executed against the "authors" table. Other possible options include "UPDATE" and "DELETE", which would be triggered when one/more rows in the "authors" table were either updated or deleted.
If we wanted, we could handle more than one type of query in one trigger. For example, to handle both "INSERT" and "UPDATE", we would use "FOR INSERT, UPDATE".
AS
DECLARE @newName VARCHAR(100)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)
Any code after the "AS" keyword is actually executed when the trigger is called. It’s important to note that this part of the trigger can contain any code that a standard stored procedure could contain. You can also call stored procedures using the "EXEC" command from within the body of the trigger.
We have created a new variable named "newName". "newName" is a variable length character value that can hold a maximum of one hundred characters. On the next line, we assign the value of an SQL query to the "newName" variable.
SELECT au_fName + ' ' + au_lName FROM Inserted
We can see that this SQL command retrieves the au_fName and au_lName fields from the "Inserted" table. The "Inserted" table is a virtual table which contains all of the fields and values from the actual "INSERT" command that made SQL Server call the trigger in the first place.
To understand what I mean, let's take a look at the design of the actual "authors" table in the "pubs" database. Right click on it and choose Design Table:
A typical "INSERT" query to add a record to the "authors" table might look like this:
INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) VALUES('172-85-4534', 'Doe', 'John', '123456', '1 Black Street', 'Doeville', 'CA', '90210', 0)
When SQL server processes this "INSERT" command, it creates a new virtual table, which contains all nine of the fields in the "INSERT" command. This table is named "Inserted", and is passed to the trig_addAuthor trigger. The table is named "Inserted" because it contains all of the newly added fields and values from our "INSERT" command.
If we created a trigger that was activated when we deleted a record from the "authors table (using the "FOR DELETE" syntax), then the virtual table would contain all of the fields and values from the deleted record(s), and would be named "Deleted".
Likewise, if we created a trigger for when an authors details were updated (using the "FOR UPDATE" syntax), then both the "Inserted" and "Deleted" virtual tables would be created and available from within the trigger. The "Deleted" table would contain all of the fields and values for the row(s) before they were updated, and the "Updated" table would contain the new row(s) with the updated fields and values.
When dealing with triggers, you must understand how they actually operate on the data contained within their virtual tables. Let’s say that we run an "UPDATE" command on the "authors" table, which has a trigger attached to it. The "UPDATE" command might affect more than one row.
When this is the case, the "UPDATE" trigger is called for each row that was affected by the update command. So, at any one time, each trigger only deals with one row.
PRINT 'New author "' + @newName + '" added.'
Lastly, we print the "newName" variable, which now contains the full name of the new author that has just been added.
To test our new trigger, start Query Analyzer (Start -> Programs -> Microsoft SQL Server 7.0 -> Query Analyzer) and connect to your database server. Enter the following code into the SQL query pane:
USE pubs
GO
SET NOCOUNT ON
INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES('172-85-4534', 'Doe', 'John', '123456', '1 Black Street', 'Doeville', 'CA', '90210', 0)
Click the "Play" button, or press the F5 function key to execute our "INSERT" statement. SQL Server will add the new record to the "authors" table, automatically calling our "trig_addAuthor" trigger once it's done. This is shown in the example below:
Now that we understand how an "INSERT" trigger works, let's take a look at "UPDATE" and "DELETE" triggers. Here's an "UPDATE" trigger:
CREATE TRIGGER trig_updateAuthor
ON authors
FOR UPDATE
AS
DECLARE @oldName VARCHAR(100)
DECLARE @newName VARCHAR(100)
IF NOT UPDATE(au_fName) AND NOT UPDATE(au_lName)
BEGIN
RETURN
END
SELECT @oldName = (SELECT au_fName + ' ' + au_lName FROM Deleted)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)
PRINT 'Name changed from "' + @oldName + '" to "' + @newName + '"'
This trigger would automatically be executed whenever we updated one/more records in the "authors" table. It starts out by creating two new variables: oldName and newName. The "UPDATE" function is used to check whether or not the "au_fName" and "au_lName" fields have been updated by the "UPDATE" query that executed the "trig_updateAuthor" trigger. If both fields haven't, then the trigger returns control to SQL server.
As I already mentioned, "UPDATE" triggers have access to two virtual tables: Deleted (which contains all of the fields and values for the records before they were updated), and Inserted (which contains all of the fields and values for the records after they have been updated). We get the value of the users name before the update from the "Deleted" table and store it in the "oldName" variable.
The updated name is stored in the "newName" variable, and is extracted from the virtual table, "Inserted". Lastly, both the authors name before and after the update query are printed.
So, if we ran an update query (through Query Analyzer) like this:
UPDATE authors
SET au_lName = 'Black'
WHERE au_id = '172-32-1176'
... then Query Analyzer would display the following text in the results pane:
Name changed from "John Doe" to "John Black"
Update triggers can also be used to check field constraints and relationships. The "contract" field of the "authors" table is a bit field representing whether or not this author has a contract with their publisher. The publisher may require notification of when an author who is on contract is removed from the "authors" table.
We could create a "DELETE" trigger on the "authors" table that would do this for us automatically:
CREATE TRIGGER trig_delAuthor
ON authors
FOR DELETE
AS
DECLARE @isOnContract BIT
SELECT @isOnContract = (SELECT contract FROM Deleted)
IF(@isOnContract = 1)
BEGIN
PRINT "Code to notify publisher goes here"
END
The "DELETE" trigger follows the same format and keyword syntax as the "INSERT" and "UPDATE" triggers. The only difference is that the "DELETE" trigger has access to the virtual table "Deleted", which contains all of the deleted rows from the "DELETE" command that triggered the "trig_delAuthor" trigger in the first place.
When triggers are used correctly, they can save a lot of development work. One of the main benefits of using triggers is that they are stored in a central repository (the database), meaning that they are accessible from all client applications / web pages that can connect to the database.
Before triggers came along, if we had a table that needed to be updated and we wanted to perform some actions after that update, then we would have to "hard code" the extra SQL into our application. This meant that if we wanted to change the code later down the track, then each client would need the updated version of our application. This is both annoying and time consuming.
If you're in the field of database design / development, and haven't seen triggers before, then you should take a look at some of the books and links below. They will give you some great information and examples about triggers and how to properly integrate them into your n-Tier applications.
Triggers allow us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table. In this article David will describe exactly what triggers are, he will show you how to create new triggers from scratch, how to test triggers, and also provide you with some valuable links/books to help you learn more about triggers.Designing a database for a large-scale web application is a monstrous task to say the least. It can however, be made easier by taking full advantage of the many tools, utilities and built-in objects that come with a Relational Database Management System (RDBMS), such as Microsoft SQL Server 7/2000.
One of these objects that many developers overlook is the trigger. Triggers are "attached" to a table and allow us to setup our database in such a way that whenever a record is added, updated, or deleted from a table, then SQL server will automatically execute a batch of SQL code after that table modification takes place.
We can do some really powerful things with the help of triggers. In this article I will describe exactly what triggers are, show you how to create new triggers from scratch, how to test those triggers, and also provide you with some valuable links/books to learn more about triggers.
A trigger is an object contained within an SQL Server database that is used to execute a batch of SQL code whenever a specific event occurs. As the name suggests, a trigger is “fired” whenever an INSERT, UPDATE, or DELETE SQL command is executed against a specific table.
Triggers are associated with a single table, and are automatically executed internally by SQL Server. Let’s create a very basic trigger now (I am using Microsoft SQL Server 7.0 on a Windows 2000 machine).
Start by opening Enterprise Manager (Start -> Programs -> Microsoft SQL Server 7.0 -> Enterprise Manager). In this example we will create our trigger against the “authors” table of the "pubs" database, so drill down through the tree view in the left pane until you can see the "“authors" table of the "pubs" database in the right pane, like this:
Next, right click on the "authors" table and choose All Tasks -> Manage Triggers... this will open the trigger properties window, which allows us to create a new trigger:
Delete all the text in the text box; we won’t need it because we’re creating our trigger from absolute scratch. All triggers are created using the "CREATE TRIGGER" command. The syntax of the “"REATE TRIGGER" command is shown below:
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
It looks really confusing, but it’s actually quite simple. I won’t go into detail about it, but you can visit http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp for the full explanation. Enter the following SQL code into the text box:
CREATE TRIGGER trig_addAuthor
ON authors
FOR INSERT
AS
-- Get the first and last name of new author
DECLARE @newName VARCHAR(100)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)
-- Print the name of the new author
PRINT 'New author "' + @newName + '" added.'
Click on the "OK" button. We have just created a new trigger named "trig_addAuthor", which is attached to the "authors" table of the "pubs" database. Whenever a new record is added to the "authors" table, SQL Server will automatically execute our trigger. Let’s discuss the actual SQL code that makes up the trigger:
CREATE TRIGGER trig_addAuthor
ON authors
These two lines tell SQL server that we want to create a new trigger object named "trig_addAuthor", which will be attached to the "authors" table.
FOR INSERT
Here, we have specified that our trigger will be executed whenever an "INSERT" command is executed against the "authors" table. Other possible options include "UPDATE" and "DELETE", which would be triggered when one/more rows in the "authors" table were either updated or deleted.
If we wanted, we could handle more than one type of query in one trigger. For example, to handle both "INSERT" and "UPDATE", we would use "FOR INSERT, UPDATE".
AS
DECLARE @newName VARCHAR(100)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)
Any code after the "AS" keyword is actually executed when the trigger is called. It’s important to note that this part of the trigger can contain any code that a standard stored procedure could contain. You can also call stored procedures using the "EXEC" command from within the body of the trigger.
We have created a new variable named "newName". "newName" is a variable length character value that can hold a maximum of one hundred characters. On the next line, we assign the value of an SQL query to the "newName" variable.
SELECT au_fName + ' ' + au_lName FROM Inserted
We can see that this SQL command retrieves the au_fName and au_lName fields from the "Inserted" table. The "Inserted" table is a virtual table which contains all of the fields and values from the actual "INSERT" command that made SQL Server call the trigger in the first place.
To understand what I mean, let's take a look at the design of the actual "authors" table in the "pubs" database. Right click on it and choose Design Table:
A typical "INSERT" query to add a record to the "authors" table might look like this:
INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) VALUES('172-85-4534', 'Doe', 'John', '123456', '1 Black Street', 'Doeville', 'CA', '90210', 0)
When SQL server processes this "INSERT" command, it creates a new virtual table, which contains all nine of the fields in the "INSERT" command. This table is named "Inserted", and is passed to the trig_addAuthor trigger. The table is named "Inserted" because it contains all of the newly added fields and values from our "INSERT" command.
If we created a trigger that was activated when we deleted a record from the "authors table (using the "FOR DELETE" syntax), then the virtual table would contain all of the fields and values from the deleted record(s), and would be named "Deleted".
Likewise, if we created a trigger for when an authors details were updated (using the "FOR UPDATE" syntax), then both the "Inserted" and "Deleted" virtual tables would be created and available from within the trigger. The "Deleted" table would contain all of the fields and values for the row(s) before they were updated, and the "Updated" table would contain the new row(s) with the updated fields and values.
When dealing with triggers, you must understand how they actually operate on the data contained within their virtual tables. Let’s say that we run an "UPDATE" command on the "authors" table, which has a trigger attached to it. The "UPDATE" command might affect more than one row.
When this is the case, the "UPDATE" trigger is called for each row that was affected by the update command. So, at any one time, each trigger only deals with one row.
PRINT 'New author "' + @newName + '" added.'
Lastly, we print the "newName" variable, which now contains the full name of the new author that has just been added.
To test our new trigger, start Query Analyzer (Start -> Programs -> Microsoft SQL Server 7.0 -> Query Analyzer) and connect to your database server. Enter the following code into the SQL query pane:
USE pubs
GO
SET NOCOUNT ON
INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES('172-85-4534', 'Doe', 'John', '123456', '1 Black Street', 'Doeville', 'CA', '90210', 0)
Click the "Play" button, or press the F5 function key to execute our "INSERT" statement. SQL Server will add the new record to the "authors" table, automatically calling our "trig_addAuthor" trigger once it's done. This is shown in the example below:
Now that we understand how an "INSERT" trigger works, let's take a look at "UPDATE" and "DELETE" triggers. Here's an "UPDATE" trigger:
CREATE TRIGGER trig_updateAuthor
ON authors
FOR UPDATE
AS
DECLARE @oldName VARCHAR(100)
DECLARE @newName VARCHAR(100)
IF NOT UPDATE(au_fName) AND NOT UPDATE(au_lName)
BEGIN
RETURN
END
SELECT @oldName = (SELECT au_fName + ' ' + au_lName FROM Deleted)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)
PRINT 'Name changed from "' + @oldName + '" to "' + @newName + '"'
This trigger would automatically be executed whenever we updated one/more records in the "authors" table. It starts out by creating two new variables: oldName and newName. The "UPDATE" function is used to check whether or not the "au_fName" and "au_lName" fields have been updated by the "UPDATE" query that executed the "trig_updateAuthor" trigger. If both fields haven't, then the trigger returns control to SQL server.
As I already mentioned, "UPDATE" triggers have access to two virtual tables: Deleted (which contains all of the fields and values for the records before they were updated), and Inserted (which contains all of the fields and values for the records after they have been updated). We get the value of the users name before the update from the "Deleted" table and store it in the "oldName" variable.
The updated name is stored in the "newName" variable, and is extracted from the virtual table, "Inserted". Lastly, both the authors name before and after the update query are printed.
So, if we ran an update query (through Query Analyzer) like this:
UPDATE authors
SET au_lName = 'Black'
WHERE au_id = '172-32-1176'
... then Query Analyzer would display the following text in the results pane:
Name changed from "John Doe" to "John Black"
Update triggers can also be used to check field constraints and relationships. The "contract" field of the "authors" table is a bit field representing whether or not this author has a contract with their publisher. The publisher may require notification of when an author who is on contract is removed from the "authors" table.
We could create a "DELETE" trigger on the "authors" table that would do this for us automatically:
CREATE TRIGGER trig_delAuthor
ON authors
FOR DELETE
AS
DECLARE @isOnContract BIT
SELECT @isOnContract = (SELECT contract FROM Deleted)
IF(@isOnContract = 1)
BEGIN
PRINT "Code to notify publisher goes here"
END
The "DELETE" trigger follows the same format and keyword syntax as the "INSERT" and "UPDATE" triggers. The only difference is that the "DELETE" trigger has access to the virtual table "Deleted", which contains all of the deleted rows from the "DELETE" command that triggered the "trig_delAuthor" trigger in the first place.
When triggers are used correctly, they can save a lot of development work. One of the main benefits of using triggers is that they are stored in a central repository (the database), meaning that they are accessible from all client applications / web pages that can connect to the database.
Before triggers came along, if we had a table that needed to be updated and we wanted to perform some actions after that update, then we would have to "hard code" the extra SQL into our application. This meant that if we wanted to change the code later down the track, then each client would need the updated version of our application. This is both annoying and time consuming.
If you're in the field of database design / development, and haven't seen triggers before, then you should take a look at some of the books and links below. They will give you some great information and examples about triggers and how to properly integrate them into your n-Tier applications.
Random Number Generator
There are many methods to generate random number in SQL Server.
Method 1 : Generate Random Numbers (Int) between Rang
---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random
Method 2 : Generate Random Float Numbers
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
Method 3 : Random Numbers Quick Scripts
---- random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()
-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
--random integer BETWEEN 0
AND 20 - [0, 20]
SELECT CONVERT(INT, (20+1)*RAND())
----random integer BETWEEN 10
AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND())
Method 4 : Random Numbers (Float, Int) Tables Based with Time
DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum
Method 5 : Random number on a per row basis
---- The distribution is pretty good however there are the occasional peaks.
---- If you want to change the range of values just change the 1000 to the maximum value you want.
---- Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber
Method 1 : Generate Random Numbers (Int) between Rang
---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random
Method 2 : Generate Random Float Numbers
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
Method 3 : Random Numbers Quick Scripts
---- random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()
-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
--random integer BETWEEN 0
AND 20 - [0, 20]
SELECT CONVERT(INT, (20+1)*RAND())
----random integer BETWEEN 10
AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND())
Method 4 : Random Numbers (Float, Int) Tables Based with Time
DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum
Method 5 : Random number on a per row basis
---- The distribution is pretty good however there are the occasional peaks.
---- If you want to change the range of values just change the 1000 to the maximum value you want.
---- Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber
Tuesday, February 23
Limitation on Views
Views.
The ANSI_NULLS and QUOTED_IDENTIFIER options must be turned on when the view is created. Following SP will turn those options on.
sp_dboption 'ANSI_NULLS', TRUE
sp_dboption 'QUOTED_IDENTIFIER', TRUE
The ANSI_NULLS option must have been turned on during the creation of all the tables that are referenced by the view.
All the tables referenced by the view must be in the same database as the view.
All the tables referenced by the view must have the same owner as the view.
Indexed view must be created with the SCHEMABINDING option. This option prohibits the schema of the base tables from being changed (adding or dropping a column, for instance). To change the tables, Indexed view must be dropped.
Any user-defined functions referenced in the view must have been created with the SCHEMABINDING option as well.
Any functions referenced in an indexed view must be deterministic; deterministic functions return the same value each time they’re invoked with the same arguments.
A column can not be referenced twice in the SELECT statement unless all references, or all but one reference, to the column are made in a complex expression.
Illegal:
SELECT qty, orderid, qty
Legal:
SELECT qty, orderid, SUM(qty)
You can’t use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, or COMPUTE BY.
The AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP aggregate functions aren’t allowed in the SELECT statement.
A SUM() that references a nullable expression isn’t allowed.
CONTAINS and FREETEXT aren’t allowed in the SELECT statement.
If you use GROUP BY, you can’t use HAVING, ROLLUP, or CUBE, and you must use COUNT_BIG() in the select list.
You can’t modify more than one table at a time through a view.
If your view is based on aggregate functions, you can’t use it to modify data.
If your view is based on a table that contains fields that don’t allow null values yet your view doesn’t display those fields, then you won’t be able to insert new data.
The ANSI_NULLS and QUOTED_IDENTIFIER options must be turned on when the view is created. Following SP will turn those options on.
sp_dboption 'ANSI_NULLS', TRUE
sp_dboption 'QUOTED_IDENTIFIER', TRUE
The ANSI_NULLS option must have been turned on during the creation of all the tables that are referenced by the view.
All the tables referenced by the view must be in the same database as the view.
All the tables referenced by the view must have the same owner as the view.
Indexed view must be created with the SCHEMABINDING option. This option prohibits the schema of the base tables from being changed (adding or dropping a column, for instance). To change the tables, Indexed view must be dropped.
Any user-defined functions referenced in the view must have been created with the SCHEMABINDING option as well.
Any functions referenced in an indexed view must be deterministic; deterministic functions return the same value each time they’re invoked with the same arguments.
A column can not be referenced twice in the SELECT statement unless all references, or all but one reference, to the column are made in a complex expression.
Illegal:
SELECT qty, orderid, qty
Legal:
SELECT qty, orderid, SUM(qty)
You can’t use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, or COMPUTE BY.
The AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP aggregate functions aren’t allowed in the SELECT statement.
A SUM() that references a nullable expression isn’t allowed.
CONTAINS and FREETEXT aren’t allowed in the SELECT statement.
If you use GROUP BY, you can’t use HAVING, ROLLUP, or CUBE, and you must use COUNT_BIG() in the select list.
You can’t modify more than one table at a time through a view.
If your view is based on aggregate functions, you can’t use it to modify data.
If your view is based on a table that contains fields that don’t allow null values yet your view doesn’t display those fields, then you won’t be able to insert new data.
Subscribe to:
Posts (Atom)