Basic
Walkthrough SQLServer Part-4
In
previous part we had created the database (single table database) and populated
the table with some data. The data seemed all right and we had nice time
retrieving the data. Except one that (friend of friend)’s last name begins with
small ‘w’. We will correct this and move on to creating triggers and other
stuff.
Let
us try updating the last name with capital ‘W’. We will see contents and update
the row based on some unique parameters. Which means that while updating if our
predicate selects more than one row, all selected rows will be updated. So be
careful while identifying the target row.
1> Update
ADDRESS SET LNAME='Walker' Where PHONE='419-932-9322' 2> go (1 rows
affected) |
Thank God. The correction
was cool.
To have a look at the modified data issue the command: SELECT * FROM
ADDRESS. The * basically means all columns.
1> select *
from address 2> go FNAME MNAME LNAME PHONE CELL ADDR ----------------
------------ ---------------- --------------- ---------------
----------------------------------- David M. Brown 905-264-2644 647-746-7466 1216 Morning Star Drive Miss,ON,CA Dilshaad Sufi Akhtar 905-932-9322 416-417-4177 3456 Horner Ave Etobicoke,ON,CA John D Walker 419-932-9322 647-417-4879 promised help with JOB Sabar Raikoti 905-509-5099 419-914-9144 1032 Mavis Rd Mississauga,ON,CA Tai Tang 647-647-6477 416-614-6144 64 Pluto Way Brampton,ON,CA Tai Tng 647-647-6477 416-614-6144 64 Pluto Way Brampton,ON,CA (6 rows
affected) |
When you look at above output
you will find last name corrected. Secondly you see word ‘promised’
in ADDR column. That means you are free to insert any text in ADDR column.
There is no CONSTRAINT set up. Usually there are no CONSTRAINTS in text fields
like address.
There was an extra entry for Tai, with wrong last name (Tng). We
did that intentionally to test behavior of primary key. Let us delete it.
1> Delete
from ADDRESS where lname='Tng' 2> go (1 rows
affected) 1> Select
Fname,Lname,Phone,Cell from address 2> go Fname Lname Phone Cell ----------------
---------------- --------------- --------------- David Brown 905-264-2644 647-746-7466 Dilshaad Akhtar 905-932-9322 416-417-4177 John Walker 419-932-9322 647-417-4879 Sabar Raikoti 905-509-5099 419-914-9144 Tai Tang 647-647-6477 416-614-6144 (5 rows
affected) |
So,
we have seen how to remove ROWS from a table. A word of caution. Do not use
DELETE FROM ADDRESS. This will delete all rows. This command should always have
where predicate to limit the rows being deleted.
The most liked part of database is input format routines. The user
will enter the data and smart systems accept a wide variety of data in loose
format and will try its best to format it strict before inserting into table.
This seems that this is part of programming. Well, yes, but basic programming
that can be handled in small triggers. So I will help you write trigger to
adjust hyphens (‘-‘) in the input data when it is fed to PHONE and CELL
columns. Please go over SQL Server information online for triggers’ definition
and syntax for more detail.
Next step will be to create a trigger to validate the data being
input into the database table.
Create a text file with any extension having following SQL code in
it. Do not worry about everything for now. Just create the trigger definition
file and get it created. This trigger will check for existence of hyphens in
appropriate position in phone numbers.
CREATE TRIGGER
INS_CELL_PHONE ON ADDRESS FOR INSERT AS declare @phone CHAR(15),@cell CHAR(15); set @phone = (select phone from inserted); set @cell = (select cell from inserted); IF (SUBSTRING(@phone,4,1)<>'-' OR
SUBSTRING(@phone,8,1)<>'-' OR SUBSTRING(@cell,4,1)<>'-' OR
SUBSTRING(@cell,8,1)<>'-' )
RAISERROR ('Phone Numbers Expected as 999-999-999',201,10); go |
This will create a trigger
which will look far hyphens (‘-‘) at specified locations. If any of hyphens are missing,
trigger will raise an error with message as specified as MESSAGE_TEXT.
To create the trigger, I suppose you
have stored the file as INS_PHONE.TRIG in current folder. See the syntax and
note that we had ended the file with @. So calling this SQL file we specify the
terminator (@) as below.
C:\Documents and
Settings\user1>sqlcmd -d sample -i ins_phone.trig C:\Documents and
Settings\user1> |
Please
note that to run SQL files you have to run it from Command Line and not sqlcmd.
These two windows are different although they look the same. If you have
started sqlcmd then you may revert back to Command Line by typing QUIT. So now
trigger has been created which checks for ‘-‘
at 4th and 8th position in PHONE and CELL. If
there is any one missing, trigger will force rejection of whole row and nothing
will be added.
1> insert
into address values ('Gurmit','S','Randhawa','416-742-9242','6477204020','1230
The Walkers Road') 2> go Msg
50000, Level 20, State 10, Server XPSQL1\SQLEXPRESS, Procedure
INS_CELL_PHONE, Line 11 Phone
Numbers Expected as 999-999-999 Msg
2745, Level 16, State 2, Server XPSQL1\SQLEXPRESS, Procedure INS_CELL_PHONE,
Line 11 Process
ID 51 has raised user error 50000, severity 20. SQL Server is terminating
this process. |
Let
us try little different. This time we won’t provide the CELL number.
1> insert
into address values ( 'Gurmit', 'S', 'Randhawa', '416-742-9242','','1230 The
Walkers Road') 2> go Msg
50000, Level 20, State 10, Server XPSQL1\SQLEXPRESS, Procedure
INS_CELL_PHONE, Line 11 Phone
Numbers Expected as 999-999-999 Msg
2745, Level 16, State 2, Server XPSQL1\SQLEXPRESS, Procedure INS_CELL_PHONE,
Line 11Process ID 51 has raised user error 50000, severity 20. SQL Server is
terminating this process. |
Same, because trigger wants
two hyphens in both numbers. So now you are forced tom enter the phone numbers
exactly as 999-999-9999. By the way, we have designed the trigger
just for practice, so we kept it simple; and you can provide any bad data
except ‘-‘ on 4th and 8th places in phone numbers.
Now try providing the data in strict format.
1>
insert into address values ('Gurmit','S','Randhawa','416-742-9242','647-720-4020','1230
The Walkers Road') 2>
go (1
rows affected) |
Cool, This works, and is much better
because the chances of omission are ruled out this way.
Triggers are not primarily for this
purpose. However being an database object it works faster and ensures data
integrity; these triggers can be put to different uses. Normally triggers are
used to make calculations based on some columns and populate the other columns
with the result. More sophisticated use can be thought of updating other tables
based on transaction data entered into main table (usually called TRANSACTION
table).
As a last section of this
walkthrough part I would like to mention here that entering all data on command
line is usually not welcome. But the good thing is that if you do it manually
you get more familiar with your database objects; i.e. tables, views, triggers
and sequences etc.
Still we do not have to go long way.
If you so wish, you can use the batch file listed below. This one will allow
you to enter data into your table. I will keep it simple for this tutorial.
@ echo
off :another set /p
fname=First Name : if [%fname%]==[]
goto done set /p
mname=Middle Name : set /p
lname=Last Name : set /p
phone=Phone Number : set /p cell=Cell
Number : set /p
addr=Address : set /p ok= Ok ? if [%ok%]==[y]
goto doit if [%ok%]==[Y]
goto doit if [%ok%]==[Yes]
goto doit if [%ok%]==[yes]
goto doit if [%ok%]==[YES]
goto doit goto redo :doit Sqlcmd -d sample
-Q "insert into address values ('%fname%','%mname%','%lname%','%phone%','%cell%','%addr%')" echo. :redo set fname= set mname= set lname= set phone= set cell= set addr= set ok= goto another :done set fname= set mname= set lname= set phone= set cell= set addr= set ok= |
Please take care saving this
script. This will need .bat or .cmd filename extension.
Go ahead and practice with data entry. Remember when you want to finish, enter
nothing in First Name :. If you miss it then say no to ‘Ok ?’ prompt and then give it empty First Name again. This batch
file will keep looping until you provide empty (NULL) for First Name. For each
entry you will be expecting following string from SQLServer. SQLServer confirms
the successful entry this way. Anything else will signal an error.
(1 rows
affected) |
If
any constraint is violated, something like below message will be returned. This
gives clear idea what may have gone wrong.
Msg 2627, Level
14, State 1, Server XPSQL1\SQLEXPRESS, Line 1 Violation of
PRIMARY KEY constraint 'My_FNAME_LNAME_PK'. Cannot insert duplicate key in object
'dbo.ADDRESS'. The statement
has been terminated. |
If you see an error then you
need to repeat the entry. Keep in mind that you have a trigger in action to
check your phone numbers. That should be good enough for this part of tutorial.
In next part, we will see how we can build a simple application to retrieve
data from our database remotely.
No comments:
Post a Comment