92 lines
3.1 KiB
MySQL
92 lines
3.1 KiB
MySQL
|
USE Metro
|
||
|
|
GO
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TRIGGER t_add_stanciya
|
||
|
|
ON stanciya INSTEAD OF INSERT
|
||
|
|
AS
|
||
|
|
DECLARE @kod int, @name varchar(20), @paspotok int, @kod_nazad int, @rast_vpered int, @rast_nazad int, @stat int, @start_time time, @finish_time time
|
||
|
|
SELECT @kod = kod, @name=name, @paspotok=paspotok, @kod_nazad = kod_nazad, @rast_vpered = rast_vpered, @rast_nazad = rast_nazad, @stat=stat, @start_time=start_time, @finish_time=finish_time
|
||
|
|
FROM inserted
|
||
|
|
IF (@kod_nazad IS NULL or @kod_nazad=0)
|
||
|
|
INSERT INTO stanciya
|
||
|
|
VALUES (@kod, @name, @paspotok, NULL, NULL, NULL, NULL, @stat, @start_time, @finish_time)
|
||
|
|
ELSE
|
||
|
|
BEGIN
|
||
|
|
DECLARE @s_kod int, @s_kod_vpered int, @s_kod_nazad int, @s_rast_vpered int, @s_rast_nazad int
|
||
|
|
DECLARE @CURSOR CURSOR
|
||
|
|
SET @CURSOR = CURSOR SCROLL
|
||
|
|
FOR
|
||
|
|
SELECT kod, kod_vpered, kod_nazad, rast_vpered, rast_nazad
|
||
|
|
FROM stanciya
|
||
|
|
OPEN @CURSOR
|
||
|
|
FETCH NEXT FROM @CURSOR INTO @s_kod, @s_kod_vpered, @s_kod_nazad, @s_rast_vpered, @s_rast_nazad
|
||
|
|
WHILE @@FETCH_STATUS = 0
|
||
|
|
BEGIN
|
||
|
|
IF (@s_kod=@kod_nazad)
|
||
|
|
BEGIN
|
||
|
|
IF @rast_vpered IS NOT NULL
|
||
|
|
BEGIN
|
||
|
|
DECLARE @kod_vpered int
|
||
|
|
SELECT @kod_vpered = kod_vpered
|
||
|
|
FROM stanciya
|
||
|
|
WHERE kod=@s_kod
|
||
|
|
UPDATE stanciya SET kod_nazad = @kod WHERE kod = @kod_vpered
|
||
|
|
UPDATE stanciya SET rast_nazad = @rast_vpered WHERE kod = @kod_vpered
|
||
|
|
END
|
||
|
|
UPDATE stanciya SET kod_vpered = @kod WHERE kod=@s_kod
|
||
|
|
UPDATE stanciya SET rast_vpered = @rast_nazad WHERE kod=@s_kod
|
||
|
|
INSERT INTO stanciya
|
||
|
|
VALUES (@kod, @name, @paspotok, @kod_vpered, @rast_vpered, @kod_nazad, @rast_nazad, @stat, @start_time, @finish_time)
|
||
|
|
BREAK
|
||
|
|
END
|
||
|
|
FETCH NEXT FROM @CURSOR INTO @s_kod, @s_kod_vpered, @s_kod_nazad, @s_rast_vpered, @s_rast_nazad
|
||
|
|
END
|
||
|
|
CLOSE @CURSOR
|
||
|
|
END
|
||
|
|
GO
|
||
|
|
|
||
|
|
CREATE TRIGGER t_del_typsostav
|
||
|
|
ON typsostav INSTEAD OF DELETE
|
||
|
|
AS
|
||
|
|
DECLARE @typkod INT
|
||
|
|
SELECT @typkod=typkod
|
||
|
|
FROM deleted
|
||
|
|
UPDATE mashinist
|
||
|
|
SET serial=NULL
|
||
|
|
WHERE serial IN (SELECT serial FROM psostav WHERE typkod=@typkod)
|
||
|
|
DELETE psostav
|
||
|
|
WHERE typkod=@typkod
|
||
|
|
DELETE typsostav
|
||
|
|
WHERE typkod=@typkod
|
||
|
|
GO
|
||
|
|
|
||
|
|
CREATE TRIGGER t_del_stanciya
|
||
|
|
ON stanciya INSTEAD OF DELETE
|
||
|
|
AS
|
||
|
|
DECLARE @kod int, @d_kod_vpered int, @d_kod_nazad int, @d_rast_vpered int, @d_rast_nazad int
|
||
|
|
SELECT @kod=kod, @d_kod_nazad=kod_nazad, @d_kod_vpered=kod_vpered, @d_rast_vpered=rast_vpered, @d_rast_nazad=rast_nazad FROM deleted
|
||
|
|
DECLARE @s_kod int, @s_kod_vpered int, @s_kod_nazad int, @s_rast_vpered int, @s_rast_nazad int
|
||
|
|
DECLARE @CURSOR CURSOR
|
||
|
|
SET @CURSOR = CURSOR SCROLL
|
||
|
|
FOR
|
||
|
|
SELECT kod, kod_vpered, kod_nazad, rast_vpered, rast_nazad
|
||
|
|
FROM stanciya
|
||
|
|
OPEN @CURSOR
|
||
|
|
FETCH NEXT FROM @CURSOR INTO @s_kod, @s_kod_vpered, @s_kod_nazad, @s_rast_vpered, @s_rast_nazad
|
||
|
|
WHILE @@FETCH_STATUS = 0
|
||
|
|
BEGIN
|
||
|
|
IF @s_kod=@d_kod_nazad
|
||
|
|
UPDATE stanciya
|
||
|
|
SET kod_vpered=@d_kod_vpered, rast_vpered=rast_vpered+@d_rast_vpered
|
||
|
|
WHERE kod=@s_kod
|
||
|
|
IF @s_kod=@d_kod_vpered
|
||
|
|
UPDATE stanciya
|
||
|
|
SET kod_nazad=@d_kod_nazad, rast_nazad=rast_nazad+@d_rast_nazad
|
||
|
|
WHERE kod=@s_kod
|
||
|
|
FETCH NEXT FROM @CURSOR INTO @s_kod, @s_kod_vpered, @s_kod_nazad, @s_rast_vpered, @s_rast_nazad
|
||
|
|
END
|
||
|
|
DELETE stanciya
|
||
|
|
WHERE kod=@kod
|
||
|
|
CLOSE @CURSOR
|
||
|
|
GO
|