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>