SQL/TRIGGERS

(Home)
Top 3 Resources
Resource LearningComputer.com
Resource MSDN
Resource SQL Server Performance.com
Quick Reference
   
Code
Delete Trigger CREATE TRIGGER tblCalibrationRecords_DTrig ON dbo.tblCalibrationRecords FOR DELETE AS
SET NOCOUNT ON
/* * CASCADE DELETES TO 'tblTraceabilityLog' */
DELETE tblTraceabilityLog FROM deleted, tblTraceabilityLog WHERE deleted.CalibrationID = tblTraceabilityLog.CalibrationID

/* * CASCADE DELETES TO 'tblCertificateStandards' */
DELETE tblCertificateStandards FROM deleted, tblCertificateStandards WHERE deleted.CalibrationID = tblCertificateStandards.CalibrationID

/* * CASCADE DELETES TO 'tblCalibrationReportData' */
DELETE tblCalibrationReportData FROM deleted, tblCalibrationReportData WHERE deleted.CalibrationID = tblCalibrationReportData.CalibrationID
Insert Trigger CREATE TRIGGER tblCalibrationRecords_ITrig ON dbo.tblCalibrationRecords FOR INSERT AS
SET NOCOUNT ON
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tblAssets' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblAssets, inserted WHERE (tblAssets.AssetID = inserted.AssetID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tblAssets''.'
ROLLBACK TRANSACTION
END

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tblClient' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblClient, inserted WHERE (tblClient.ClientAccountNumber = inserted.ClientAccountNumber))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tblClient''.'
ROLLBACK TRANSACTION
END
Update Trigger CREATE TRIGGER "tblCalibrationRecords_UTrig" ON dbo.tblCalibrationRecords FOR UPDATE AS
SET NOCOUNT ON
/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tblAssets' */
IF UPDATE(AssetID)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblAssets, inserted WHERE (tblAssets.AssetID = inserted.AssetID))
BEGIN
RAISERROR 44446 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tblAssets''.'
ROLLBACK TRANSACTION
END
END

/* * CASCADE UPDATES TO 'tblTraceabilityLog' */
IF UPDATE(CalibrationID)
BEGIN
UPDATE tblTraceabilityLog
SET tblTraceabilityLog.CalibrationID = inserted.CalibrationID
FROM tblTraceabilityLog, deleted, inserted
WHERE deleted.CalibrationID = tblTraceabilityLog.CalibrationID
END

/* * CASCADE UPDATES TO 'tblCertificateStandards' */
IF UPDATE(CalibrationID)
BEGIN
UPDATE tblCertificateStandards
SET tblCertificateStandards.CalibrationID = inserted.CalibrationID
FROM tblCertificateStandards, deleted, inserted
WHERE deleted.CalibrationID = tblCertificateStandards.CalibrationID
END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tblClient' */
IF UPDATE(ClientAccountNumber)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblClient, inserted WHERE (tblClient.ClientAccountNumber = inserted.ClientAccountNumber))
BEGIN
RAISERROR 44446 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tblClient''.'
ROLLBACK TRANSACTION
END
END

/* * CASCADE UPDATES TO 'tblCalibrationReportData' */
IF UPDATE(CalibrationID)
BEGIN
UPDATE tblCalibrationReportData
SET tblCalibrationReportData.CalibrationID = inserted.CalibrationID
FROM tblCalibrationReportData, deleted, inserted
WHERE deleted.CalibrationID = tblCalibrationReportData.CalibrationID
END