Архив / Процедуры-MSSQLServer
.pdfпример MS SQL Server
tblDetail
id_d |
dname |
color |
weight |
city |
total |
|
|
|
|
|
|
1 |
винт |
красный |
12 |
Москва |
130 |
|
|
|
|
|
|
2 |
болт |
зеленый |
17 |
СПб |
200 |
3 |
гайка |
синий |
17 |
Тула |
300 |
|
|
|
|
|
|
5 |
шайба |
синий |
12 |
СПб |
600 |
6 |
гвоздь |
красный |
19 |
Москва |
0 |
|
|
|
|
|
|
7 |
винт |
зеленый |
Null |
Null |
0 |
|
|
|
|
|
|
INSERT INTO tblDetail (dname, color)
VALUES(‘винт’,‘зеленый’)
проверка правильности новых данных:
вес детали должен быть не более 1000
CREATE TRIGGER tgr_detail_insert ON tblDetail FOR INSERT
AS
DECLARE @w REAL BEGIN
SELECT @w=weight FROM INSERTED IF @w>1000
BEGIN
RAISERROR('Слишком тяжелая деталь', 16,1) ROLLBACK TRANSACTION
END
END
проверка правильности новых данных: Поставки деталей из Москвы для поставщиков со статусом менее 30 запрещены
CREATE TRIGGER tgr_delivery_insert ON tblDelivery FOR INSERT AS
DECLARE @id1 int, @id2 int, @s int, @c char(20) BEGIN
SELECT @id1=id_d, @id2=id_p FROM INSERTED SELECT @c=city FROM tblDetail WHERE id_d=@id1 SELECT @s=status FROM tblProvider WHERE id_p=@id2 IF (@c = ’Москва’) AND (@s < 30)
BEGIN
RAISERROR('Поставки деталей из Москвы для поставщиков со статусом менее 30 запрещены',16,1)
ROLLBACK TRANSACTION END
END
Имитация работы ограничения целостности для внешнего ключа
CREATE TRIGGER tgr_detail_delete ON tblDetail INSTEAD OF DELETE
AS
DECLARE @id int
DECLARE cur CURSOR FOR SELECT id_d FROM DELETED
BEGIN OPEN cur
FETCH NEXT FROM cur INTO @id WHILE @@FETCH_STATUS = 0
BEGIN
Delete from tblDelivery WHERE id_d=@id
Delete from tblDetail WHERE id_d=@id FETCH NEXT FROM cur INTO @id
END CLOSE cur
END
пример MS SQL Server
tblDetail
id_d |
dname |
|
|
color |
weight |
city |
total |
||
|
|
|
|
|
|
|
|
|
|
1 |
|
винт |
|
красный |
12 |
Москва |
130 |
||
2 |
|
болт |
|
зеленый |
17 |
СПб |
200 |
||
|
|
|
|
|
|
|
|
|
|
3 |
|
гайка |
|
синий |
17 |
Тула |
300 |
||
|
|
|
|
|
|
|
|
|
|
5 |
|
шайба |
|
синий |
12 |
СПб |
600 |
||
6 |
|
гвоздь |
|
красный |
19 |
Москва |
0 |
||
|
|
|
|
|
|
|
|
|
|
tblDelivery |
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
||
id_p |
|
id_d |
|
n |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
1 |
|
100 |
|
|
|
|
|
3 |
|
2 |
|
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
|
1 |
|
30 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
|
5 |
|
400 |
|
|
|
|
|
4 |
|
5 |
|
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
3 |
|
300 |
|
|
|
|
DELETE FROM tblDetail WHERE color = ‘синий’
пример MS SQL Server
tblDetail
|
id_d |
dname |
|
|
color |
weight |
city |
total |
||
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
винт |
|
красный |
12 |
Москва |
130 |
||
|
2 |
|
болт |
|
зеленый |
17 |
СПб |
200 |
||
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
гайка |
|
синий |
17 |
Тула |
300 |
||
|
|
|
|
|
|
|
|
|
|
|
|
5 |
|
шайба |
|
синий |
12 |
СПб |
600 |
||
|
6 |
|
гвоздь |
|
красный |
19 |
Москва |
0 |
||
|
|
|
|
|
|
|
|
|
|
|
|
tblDelivery |
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
||
|
id_p |
|
id_d |
|
n |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
1 |
|
100 |
|
|
|
|
|
|
3 |
|
2 |
|
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
|
1 |
|
30 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
|
5 |
|
400 |
|
|
|
|
|
|
4 |
|
5 |
|
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
3 |
|
300 |
|
|
|
|
DELETE FROM tblDetail WHERE color = ‘синий’
пример MS SQL Server
tblDetail
|
id_d |
dname |
|
|
color |
weight |
city |
total |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
винт |
|
красный |
12 |
Москва |
130 |
|
||
|
2 |
|
болт |
|
зеленый |
17 |
СПб |
200 |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
гайка |
|
синий |
17 |
Тула |
300 |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
5 |
|
шайба |
|
синий |
12 |
СПб |
600 |
|
||
|
6 |
|
гвоздь |
|
красный |
19 |
Москва |
0 |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
tblDelivery |
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
||
|
id_p |
|
id_d |
|
n |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
1 |
|
100 |
|
|
|
|
|
|
|
3 |
|
2 |
|
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
|
1 |
|
30 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
|
5 |
|
400 |
|
|
|
|
|
|
|
4 |
|
5 |
|
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
3 |
|
300 |
|
|
|
|
|
DELETE FROM tblDetail WHERE color = ‘синий’
пример MS SQL Server
tblDetail
|
id_d |
dname |
|
|
color |
weight |
city |
total |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
винт |
|
красный |
12 |
Москва |
130 |
|
||
|
2 |
|
болт |
|
зеленый |
17 |
СПб |
200 |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
гайка |
|
синий |
17 |
Тула |
300 |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
5 |
|
шайба |
|
синий |
12 |
СПб |
600 |
|
||
|
6 |
|
гвоздь |
|
красный |
19 |
Москва |
0 |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
tblDelivery |
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
||
|
id_p |
|
id_d |
|
n |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
1 |
|
100 |
|
|
|
|
|
|
|
3 |
|
2 |
|
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
|
1 |
|
30 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
|
5 |
|
400 |
|
|
|
|
|
|
|
4 |
|
5 |
|
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
3 |
|
300 |
|
|
|
|
|
DELETE FROM tblDetail WHERE color = ‘синий’
|
tblDetail |
пример MS SQL Server |
|
||||||||
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
id_d |
dname |
|
|
color |
weight |
city |
total |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
винт |
|
красный |
12 |
Москва |
130 |
|
||
|
2 |
|
болт |
|
зеленый |
17 |
СПб |
200 |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
гайка |
|
синий |
17 |
Тула |
300 |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
5 |
|
шайба |
|
синий |
12 |
СПб |
600 |
|
||
|
6 |
|
гвоздь |
|
красный |
19 |
Москва |
0 |
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
tblDelivery |
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
||
|
id_p |
|
id_d |
|
n |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
1 |
|
100 |
|
|
|
|
|
|
|
3 |
|
2 |
|
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
|
1 |
|
30 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
|
5 |
|
400 |
|
|
|
|
|
|
|
4 |
|
5 |
|
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
3 |
|
300 |
|
|
|
|
|
DELETE FROM tblDetail WHERE color = ‘синий’