Basic Walkthrough SQL Server: Basic Walkthrough SQLServer Part-5

Basic Walkthrough SQLServer Part-5

In this part we will study some more useful database objects. For example VIEWS, TRIGGERS, PROCEDURES and few other.

As we had an introduction to trigger creation in part 2, we will discuss how we run scripts first. SQLSERVER supports different kind of scripts. You can run system shell scripts (.bat, .cmd, .js, .vbs,.sh and many more). Let us try to understand how we can run scripts. Simple SQL script looks like

C:\Documents and Settings\user1>sqlcmd –i script.sql

use sample;

Select Fname,Lname,Phone,Cell from address;

select count(*) from address;

This was a simple SQL script. This is to be run in sqlcmd command window. The syntax has been indicated in blue. Being an example script, I just listed few commands. In actual scripts these lines are usually over hundreds.

C:\Documents and Settings\user01>sqlcmd –i script.sql -o script.out.txt

Similarly, we can use system scripts too for accomplishing same mission. Have a look at following script. This one is equivalent to the previous one.

C:\Documents and Settings\user01>Script.bat

sqlcmd -d sample -Q “Select Fname,Lname,Phone,Cell from address”

sqlcmd -d sample -Q “select count(*) from address”

This is very straightforward that we use sqlcmd keyword and removed the terminator. The scripts are usually written for various tasks including generating daily reports. As a rule of thumb, add all the commands to script file that are to be repeated in future and invoke the script using one of above syntaxes.

Let us create another trigger on TRANS table (created in part 2) to ensure that user uses in or out keywords only in code column. Using anything else will be waste of data. So, we will try to reject entry if the code is not set properly.

C:\Documents and Settings\user01>sqlcmd –td@ -f TriggerCheckCode.sql

CREATE TRIGGER Check_Code ON TRANS

FOR INSERT

declare @code CHAR(4);

set @code = (select code from inserted);

IF (upper(@code)<>'IN' AND upper(@code)<>'OUT')

RAISERROR('Code must be either "in" or "out"',20,10);

This trigger will stop us from entering wrong ‘Code’ in TRANS table. This is to ensure that data entered will be usable by the trigger update_total trigger to update TOTAL tables.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “select * from total”

WEEKNUM INCOME EXPENSE TIMEUPDATE

------- -------- -------- -----------------------

49 224.20 49.40 2018-12-04 22:25:21.000

(1 rows affected)

C:\Documents and Settings\user01>sqlcmd -d sample -Q “insert into TRANS values (120.00,'Not IN')”

Msg 2754, Level 16, State 1, Server XPSQL1\SQLEXPRESS, Procedure Check_Code, Line 9

Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.The statement has been terminated.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “insert into TRANS values (default, 120.00,'In')”

(1 rows affected)

C:\Documents and Settings\user01>sqlcmd -d sample -Q “select * from total”

WEEKNUM INCOME EXPENSE TIMEUPDATE

------- -------- -------- -----------------------

49 344.20 49.40 2018-12-04 23:43:29.500

(1 rows affected)

Cool, the transaction was applied to TOTAL table when we entered the correct code. Remember we had used ‘case free’ iN and OuT key words. It will work with any case.

Let us try to have some flavor of VIEWS. As explained earlier the VIEW is combination of columns from different tables. We will create another table called PERSONS with names of all family members. Then we will create a view joining TRANS and PERSONS tables to save info about who had carried out the transaction. For this purpose, we will add a column PID to TRANS table.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “alter table TRANS add PID CHAR(2)”

Now create table PERSONS as below.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “create table PERSONS (PID CHAR(2) not null primary key, FNAME CHAR(16), LNAME CHAR(12))”

Add some data to the table.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “insert into PERSONS values (‘00’,’David’,’Boon’)”

C:\Documents and Settings\user01>sqlcmd -d sample -Q “insert into PERSONS values (‘01’,’Marry’,’Boon’)”

C:\Documents and Settings\user01>sqlcmd -d sample -Q “insert into PERSONS values (‘02’,’John’,’Boon’)”

C:\Documents and Settings\user01>sqlcmd -d sample -Q “select * from persons”

PID FNAME LNAME

--- ---------------- ------------

00 David Boon

01 Marry Boon

02 John Boon

(3 rows affected)

Now we need to enter these PID in TRANS table to indicate who carried the transactions out. Let’s do that.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “select * from TRANS”

ID AMOUNT CODE PID

------ -------- ------ ---

0 34.40 out -

1 123.45 in -

2 100.00 in -

3 0.75 in -

4 15.00 out -

6 120.00 In -

6 record(s) selected.

C:\Documents and Settings\user01>sqlcmd -d sample -Q “update TRANS set PID='00' where ID=0”