| -- dbo.trGroups.SQL IF EXISTS ( SELECT * FROM sysobjects WHERE name = 'trGroups' AND type = 'TR') BEGIN DROP TRIGGER trGroups IF NOT EXISTS ( SELECT * FROM sysobjects WHERE name = 'trGroups' AND type = 'TR') PRINT 'DROP trGroups SUCCEEDED' ELSE PRINT 'DROP trGroups FAILED' END ELSE PRINT 'Attempting to CREATE trGroups' GO CREATE TRIGGER dbo.trGroups ON groups AFTER INSERT, UPDATE, DELETE AS -- This trigger submits a cached rates delete job for modified groups. -- It also updates the groups' last modified columns for modified groups. -- It also updates the groups' state (if currently unspecified) based on -- the (first) state extracted from groups' zip code for new/modified groups. -- No error checking is currently being performed. SET NOCOUNT ON -- stop display of rowcount messages DECLARE @groupID int, @deletedCount int, @returnCode int SELECT @deletedCount = COUNT(*) FROM deleted WITH (NOLOCK) IF @deletedCount > 0 BEGIN DECLARE groupIDCursor CURSOR FAST_FORWARD FOR SELECT DISTINCT t1.groupID FROM ( SELECT groupID FROM inserted WITH (NOLOCK) UNION SELECT groupID FROM deleted WITH (NOLOCK) ) as t1 OPEN groupIDCursor FETCH NEXT FROM groupIDCursor INTO @groupID WHILE @@FETCH_STATUS = 0 BEGIN EXEC @returnCode = dbo.spCachingModuleAddJobDeleteCachedRatesByGroupID @groupID = @groupID FETCH NEXT FROM groupIDCursor INTO @groupID END CLOSE groupIDCursor DEALLOCATE groupIDCursor END GO IF EXISTS ( SELECT * FROM sysobjects WHERE name = 'trGroups' AND type = 'TR') PRINT 'CREATE trGroups SUCCEEDED' ELSE PRINT 'CREATE trGroups FAILED' GO
触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。 Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。 1.插入操作（Insert） |