Студопедия
Случайная страница | ТОМ-1 | ТОМ-2 | ТОМ-3
АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатика
ИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханика
ОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторика
СоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансы
ХимияЧерчениеЭкологияЭкономикаЭлектроника

ПРИЛОЖЕНИЕ 4 ФАЙЛ Triggers.sql

Читайте также:
  1. Вы успешно удалили приложение Get Windows 10 !
  2. Данное приложение является официальным вызовом и основанием для командирования спортсменов, тренеров и судей на соревнования
  3. Данное приложение является официальным вызовом и основанием для командирования спортсменов, тренеров и судей на соревнования
  4. Данное приложение является официальным вызовом и основанием для командирования спортсменов, тренеров и судей на соревнования
  5. Знакомство с приложением Windows Form в Microsoft Visual Studio 2010
  6. Оценка финансовой деятельности. Приложение 16
  7. Приложение 1

USE db209

GO

 

CREATE TRIGGER [dbo].[UserSysUserTrigger]

ON [dbo].[User] FOR INSERT, UPDATE

AS

BEGIN

DECLARE @Null INT

SELECT @Null = COUNT(*) FROM INSERTED WHERE SysUserId IS NOT NULL AND SysUserId NOT IN (SELECT principal_id FROM sys.database_principals)

IF @Null > 0

RAISERROR(N'Invalid value for SysUserId field in [dbo].[User] table.', 10, 1)

END

GO

 

CREATE TRIGGER [dbo].[FileDeleteTrigger]

ON [dbo].[File] FOR DELETE

AS

BEGIN

DELETE FROM [dbo].[ProjectFile] WHERE FileId IN (SELECT Id FROM DELETED)

DELETE FROM [dbo].[TaskFile] WHERE FileId IN (SELECT Id FROM DELETED)

DELETE FROM [dbo].[CommentFile] WHERE FileId IN (SELECT Id FROM DELETED)

END

GO

 

CREATE TRIGGER [dbo].[UserDeleteTrigger]

ON [dbo].[User] FOR DELETE

AS

BEGIN

DELETE FROM [dbo].[ProjectUser] WHERE UserId IN (SELECT Id FROM DELETED)

DELETE FROM [dbo].[TaskAuditor] WHERE UserId IN (SELECT Id FROM DELETED)

DELETE FROM [dbo].[Comment] WHERE UserId IN (SELECT Id FROM DELETED)

UPDATE [dbo].[Task] SET CreatorId = NULL WHERE [CreatorId] IN (SELECT Id FROM DELETED)

UPDATE [dbo].[Task] SET PerformerId = [dbo].[GetTaskPerformer](Id, PerformerId) WHERE PerformerId IN (SELECT Id FROM DELETED)

END

GO

 

CREATE TRIGGER [dbo].[ProjectDeleteTrigger]

ON [dbo].[Project] FOR DELETE

AS

BEGIN

DELETE FROM [dbo].[Project] WHERE ParentId IN (SELECT Id FROM DELETED)

END

GO

 

CREATE TRIGGER [dbo].[ProjectUpdateTrigger]

ON [dbo].[Project] FOR UPDATE

AS

BEGIN

UPDATE [dbo].[Project] SET ModifyDate = GETDATE() WHERE Id IN (SELECT Id FROM INSERTED)

END

GO

 

CREATE TRIGGER [dbo].[TaskUpdateTrigger]

ON [dbo].[Task] FOR UPDATE

AS

BEGIN

UPDATE [dbo].[Task] SET ModifyDate = GETDATE() WHERE Id IN (SELECT Id FROM INSERTED)

END

GO

 

CREATE TRIGGER [dbo].[TaskDeleteTrigger]

ON [dbo].[Task] FOR DELETE

AS

BEGIN

DELETE FROM [dbo].[Task] WHERE ParentId IN (SELECT Id FROM DELETED)

END

GO

 

CREATE TRIGGER [dbo].[ComponentDeleteTrigger]

ON [dbo].[Component] FOR DELETE

AS

BEGIN

DELETE FROM [dbo].[Component] WHERE ParentId IN (SELECT Id FROM DELETED)

DELETE FROM [dbo].[Task] WHERE ComponentId IN (SELECT Id FROM DELETED)

END

GO

 

CREATE TRIGGER [dbo].[StateDeleteTrigger]

ON [dbo].[State] FOR DELETE

AS

BEGIN

DECLARE @deletedId INT

DECLARE DeleteCursor CURSOR LOCAL FAST_FORWARD FOR

SELECT [Id] FROM DELETED

OPEN DeleteCursor

 

FETCH NEXT FROM DeleteCursor INTO @deletedId

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO [dbo].[StateTransition]

SELECT s1.CategoryId [CategoryId],

s1.InitialStateId [InitialStateId],

s2.FinalStateId [FinalStateId]

FROM [dbo].[StateTransition] s1

JOIN [dbo].[StateTransition] s2

ON s1.CategoryId = s2.CategoryId AND s2.InitialStateId = s1.FinalStateId

WHERE s1.FinalStateId = @deletedId

 

DELETE FROM [dbo].[StateTransition] WHERE [InitialStateId] = @deletedId OR [FinalStateId] = @deletedId

 

FETCH NEXT FROM DeleteCursor INTO @deletedId

END

 

CLOSE DeleteCursor

END

GO

 

CREATE TRIGGER [dbo].[MilestoneDeleteTrigger]

ON [dbo].[Milestone] FOR DELETE

AS

BEGIN

UPDATE [dbo].[Task] SET [dbo].[Task].[MilestoneId] = NULL WHERE [dbo].[Task].[MilestoneId] IN (SELECT Id FROM DELETED)

END

GO

 



Дата добавления: 2015-10-31; просмотров: 94 | Нарушение авторских прав


Читайте в этой же книге: ХРАНИМЫЕ ПРОЦЕДУРЫ | ПРИЛОЖЕНИЕ 6 ФАЙЛ Reports.sql | ПРИЛОЖЕНИЕ 9 ФАЙЛ FillData.sql |
<== предыдущая страница | следующая страница ==>
ПРИЛОЖЕНИЕ 3 ФАЙЛ Tables.sql| ПРИЛОЖЕНИЕ 5 ФАЙЛ Procedures.sql

mybiblioteka.su - 2015-2024 год. (0.006 сек.)