Упражнение 6.
Использование триггеров для оеспечения целостности при взаимодействии нескольких БД.
-- INSERT и UPDATE потомка:
CREATE TRIGGER dbo.Child$InsertUpdate
ON dbo.Child_tbl AFTER INSERT, UPDATE AS BEGIN
DECLARE @rowsAffected int, --stores the number of rows affected
@msg varchar(2000) --used to hold the error message
SET @rowsAffected = @@rowcount
--no need to continue on if no rows affected
IF @rowsAffected=0 return
SET NOCOUNT ON --to avoid the rowcount messages
SET ROWCOUNT 0 --in case the client has modified the rowcount
BEGIN TRY
--[validation blocks]
--@numrows is part of the standard template
DECLARE @nullcnt int,
@validcnt int, @numrows int
IF update(ID)
BEGIN
--omit this check if nulls are not allowed (left in here for an example)
SELECT @nullcnt = count(*)
FROM inserted
WHERE inserted.ID is null --does not include null values
SELECT @validcnt = count(*)
FROM inserted
JOIN Parent.ID as Parent
ON inserted.ID = Parent.ID
if @validcnt + @nullcnt <> @numrows
BEGIN
IF @rowsAffected = 1
SELECT @msg = 'The inserted Callld: '
+ cast(ID as varchar (10))
+ ' is not valid in the'
+ ' Parent.ID table.'
FROM INSERTED
ELSE
SELECT @msg = 'Invalid Callld in the inserted rows.'
RAISERROR (@msg,16,1)
END
END
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRANSACTION
-- or this will not get rolled back
--EXECUTE dbo.ErrorLogSinsert
DECLARE @ERROR_MESSAGE varchar(4000)
SET @ERROR_MESSAGE = ERROR_MESSAGE()
RAISERROR (@ERROR_MESSAGE,16,1)
END CATCH
END
---------------------------------------------------------------------------------
-- UPDATE родителя:
CREATE TRIGGER dbo.Parent$Update
ON dbo.Parent_tbl AFTER INSERT, UPDATE AS BEGIN
DECLARE @rowsAffected int, --stores the number of rows affected
@msg varchar(2000) --used to hold the error message
SET @rowsAffected = @@rowcount
--no need to continue on if no rows affected
IF @rowsAffected=0 return
SET NOCOUNT ON --to avoid the rowcount messages
SET ROWCOUNT 0 --in case the client has modified the rowcount
BEGIN TRY
--[validation blocks]
--@numrows is part of the standard template
DECLARE @nullcnt int,
@validcnt int, @numrows int
if update(ID)
BEGIN
IF EXISTS (SELECT * FROM deleted
JOIN Child ON Child.ID = deleted.ID
)
BEGIN
IF @rowsAffected = 1
SELECT @msg='one row message' + inserted.ID
FROM inserted
ELSE
SELECT @msg = 'multi-row message'
RAISEERROR (@msg, 16, 1)
END
END
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRANSACTION
-- or this will not get rolled back
--EXECUTE dbo.ErrorLogSinsert
DECLARE @ERROR_MESSAGE varchar(4000)
SET @ERROR_MESSAGE = ERROR_MESSAGE()
RAISERROR (@ERROR_MESSAGE,16,1)
END CATCH
END
-- DELETE родителя:
CREATE TRIGGER dbo.Parent$Delete
ON dbo.Parent_tbl AFTER DELETE AS BEGIN
DECLARE @rowsAffected int, --stores the number of rows affected
@msg varchar(2000) --used to hold the error message
SET @rowsAffected = @@rowcount
--no need to continue on if no rows affected
IF @rowsAffected=0 return
SET NOCOUNT ON --to avoid the rowcount messages
SET ROWCOUNT 0 --in case the client has modified the rowcount
BEGIN TRY
--[validation blocks]
--@numrows is part of the standard template
DECLARE @nullcnt int,
@validcnt int, @numrows int
IF EXISTS (SELECT * FROM deleted
JOIN Child ON Child.ID = deleted.ID
)
BEGIN
IF @rowsAffected = 1
SELECT @msg='one row message' + deleted.ID
FROM inserted
ELSE
SELECT @msg = 'multi-row message'
RAISEERROR (@msg, 16, 1)
END
END
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRANSACTION
-- or this will not get rolled back
--EXECUTE dbo.ErrorLogSinsert
DECLARE @ERROR_MESSAGE varchar(4000)
SET @ERROR_MESSAGE = ERROR_MESSAGE()
RAISERROR (@ERROR_MESSAGE,16,1)
END CATCH
END