SQL Server 2000 is one of the most popular RDBMS's for the Windows platform. Never worked with SQL Server 2000 before but dying to know how? In this article Mitchell looks at how to create your first database, and also shows you how to manipulate this database through both Enterprise Manager and Query Analyzer.
Building Your First SQL Server 2000 Database - Manipulating our database (Page 4 of 5 )
On this page we're going to play around with a couple of TSQL commands and modify our database. The details of the commands you're about to see are available in the SQL Server 2000 help file, which you can access by pressing F1 in Query Analyzer.
Adding a column to our widgets table
Let's pretend that the boss of our widgets corporation wants to be able to set whether or not a particular widget is in stock. Most widgets are available, however a select few have to be imported from Widgetville first. We could add a new column called widgetAvailable with this TSQL code:
ALTER TABLE widgets
ADD widgetAvailable BIT NOT NULL DEFAULT 1
The code above creates the new widgetAvailable column and also sets the value of that column to 1 for every record that currently resides in the widgets table.
Add a trigger to our widgets table
On 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:
CREATE TRIGGER trigShowTotalWidgets
DECLARE @numWidgets INT
SELECT @numWidgets = (SELECT COUNT(*) FROM Widgets)
PRINT 'There are '+CAST(@numWidgets AS VARCHAR)+' in the widgets table'
Run the code above, clear the Query Analyzer window and then enter and run the following code:
SET NOCOUNT ON
INSERT INTO widgets(widgetName, widgetPrice, widgetAvailable)
VALUES('Maroon Widget', 21.45, 1)
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 then I've written a complete article about them. You can read that article here.
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 store procedure 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: