SQL Server/T-SQL/Transact SQL/RAISERROR

Creating a Trigger and raise an error

1> create table employee(
2>     ID          int,
3>     name        nvarchar (10),
4>     salary      int )
5> GO
1>
2> create table job(
3>     ID              int,
4>     title nvarchar  (10),
5>     averageSalary   int)
6> GO
1>
2>
3> insert into employee (ID, name, salary) values (1,  "Jason", 1234)
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (2,  "Robert", 4321)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (3,  "Celia", 5432)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (4,  "Linda", 3456)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (5,  "David", 7654)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (6,  "James", 4567)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (7,  "Alison", 8744)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (8,  "Chris", 9875)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (9,  "Mary", 2345)
2> GO
(1 rows affected)
1>
2> insert into job(ID, title, averageSalary) values(1,"Developer",3000)
3> GO
(1 rows affected)
1> insert into job(ID, title, averageSalary) values(2,"Tester", 4000)
2> GO
(1 rows affected)
1> insert into job(ID, title, averageSalary) values(3,"Designer", 5000)
2> GO
(1 rows affected)
1> insert into job(ID, title, averageSalary) values(4,"Programmer", 6000)
2> GO
(1 rows affected)
1>
2>
3> select * from employee;
4> GO
ID          name       salary
----------- ---------- -----------
          1 Jason             1234
          2 Robert            4321
          3 Celia             5432
          4 Linda             3456
          5 David             7654
          6 James             4567
          7 Alison            8744
          8 Chris             9875
          9 Mary              2345
(9 rows affected)
1> select * from job;
2> GO
ID          title      averageSalary
----------- ---------- -------------
          1 Developer           3000
          2 Tester              4000
          3 Designer            5000
          4 Programmer          6000
(4 rows affected)
1>
2>
3> -- Creating a Trigger
4>
5> CREATE TRIGGER tr_DelData
6> ON Employee
7> FOR DELETE
8> AS
9>     IF (SELECT Count(*) FROM job) > 0
10>     BEGIN
11>       RAISERROR 50009 "Cannot delete an employee"
12>       ROLLBACK TRANSACTION
13>       RETURN
14>     END
15> GO
1>
2> delete employee
3> GO
Msg 50009, Level 16, State 1, Server sqle\SQLEXPRESS, Procedure tr_DelData, Line 11
Cannot delete an employee
Msg 3609, Level 16, State 1, Server sqle\SQLEXPRESS, Line 2
The transaction ended in the trigger. The batch has been aborted.
1>
2> select * from employee
3> GO
ID          name       salary
----------- ---------- -----------
          1 Jason             1234
          2 Robert            4321
          3 Celia             5432
          4 Linda             3456
          5 David             7654
          6 James             4567
          7 Alison            8744
          8 Chris             9875
          9 Mary              2345
(9 rows affected)
1>
2> drop trigger tr_DelData
3> drop table employee;
4> drop table job;
5> GO
1>

How to raise errors

1>
2>
3> -- Replace the default error message and numbers with my own:
4>
5> CREATE PROCEDURE spRunSQL
6>     @Statement VarChar(2000) -- Input param. accepts any SQL statement.
7> AS
8>     DECLARE   @StartTime DateTime
9>             , @EndTime DateTime
10>             , @ExecutionTime Int
11>             , @ErrNum Int
12>     SET @StartTime = GetDate()
13>     EXECUTE (@Statement)
14>     SET @ErrNum = @@Error
15>     IF @ErrNum = 207      -- Bad column
16>        RAISERROR 50001 "Bad column name"
17>     ELSE IF @ErrNum = 208     -- Bad object
18>        RAISERROR 50002 "Bad object name"
19>     ELSE IF @ErrNum = 0   -- No error. Resume.
20>        BEGIN
21>         SET @EndTime = GetDate()
22>         SET @ExecutionTime = DateDiff(MilliSecond, @StartTime, @EndTime)
23>         RETURN @ExecutionTime -- Return execution time in milliseconds
24>       END
25> GO
1>
2> EXEC spRunSQL "select 1 GO"
3> GO
GO
-----------
          1
1>
2> EXEC spRunSQL "selet 1 GO"
3> GO
Msg 102, Level 15, State 1, Server sqle\SQLEXPRESS, Line 1
Incorrect syntax near "GO".
1>
2>
3> drop procedure spRunSQL
4> GO
1>
2>