Your First SQL Server 2000 Database

Add a trigger to our widgets table

One of the most powerful features of SQL Server is the trigger. A trigger is a piece of code that is fired whenever an INSERT, UPDATE or DELETE command is executed against a particular table. Let's create a trigger on our widgets table so that whenever we add a new widget to the table, SQL Server will tell us how many widgets exist in the table:

USE MYDATABASE1        
GO        
     
CREATE TRIGGER trigShowTotalWidgets        
ON widgets        
FOR INSERT        
AS        
DECLARE @numWidgets INT        
SELECT @numWidgets = (SELECT COUNT(*) FROM Widgets)        
PRINT 'There are '+CAST(@numWidgets AS VARCHAR)+' in the widgets table'        
GO

Run the code above, clear the Query Analyzer window and then enter and run the following code:

USE MYDATABASE1        
GO        
     
SET NOCOUNT ON        
     
INSERT INTO widgets(widgetName, widgetPrice, widgetAvailable)        
VALUES('Maroon Widget', 21.45, 1)        
GO

We've setup a trigger that will execute whenever an INSERT statement is executed against our widgets database. The trigger prints how many records exist in the widgets table. When you run the code above, the bottom pane of Query Analyzer should read something like "There are 7 in the widgets table". If you'd like to read more about triggers, read this article.

Backing up our database

SQL Server 2000 is one seriously robust and flexible RDBMS. It includes several stored procedures, which are simply blocks of TSQL statements grouped together. The master database contains dozens of stored procedures, and you can see them using Enterprise Manager by expanding the master database and clicking on the stored procedures node. Double click on any stored procedure in the right pane to view its code.

To backup our MyDatabase1 database, we first need to create a backup device, which is a place where the backup will reside. We can use the sp_adddumpdevice stored procedure to do this:

USE MASTER        
GO        
     
sp_addumpdevice 'disk', 'MyDatabase1_device', 'c:\MyDatabase1.dat'        
GO

In the code above, we've created a new backup device with the alias of MyDatabase1_device. We can then back up our database to this device using TSQL's BACKUP DATABASE command, like this:

BACKUP DATABASE MyDatabase1 TO MyDatabase1_device        
GO

Query Analyzer will respond with something similar to this:

Processed 96 pages for database        
'MyDatabase1', file 'MyDatabase1' on file 1.        
Processed 1 pages for database 'MyDatabase1', file        
'MyDatabase1_log' on file 1.        
BACKUP DATABASE successfully processed 97 pages in        
0.217 seconds (3.635 MB/sec).

Your database has now been backed up to a file called MyDatabase1.dat in the root of your C: drive (don't forget to delete it when you're finished experimenting!).