SQL Server输入触发器到天蓝色触发器 [英] SQL Server input trigger to azure trigger

问题描述

我刚刚将我的页面之一转移到Windows Azure帐户.一切都进行得很顺利..，直到我尝试创建一些数据.我的触发器可以与MSSQL2008很好地配合使用，但在Azure上失败了-我该如何解决此触发器:

I just transferred one of my pages to a Windows Azure Account. Everything went smooth .. until I tried to create some data. My trigger, which worked fine with MSSQL2008 fails on azure - how could I fix this trigger:

CREATE TRIGGER creator
ON someTable
FOR INSERT
AS
DECLARE @someTableID INT;
SELECT @someTableID=(SELECT someTableID FROM INSERTED)
INSERT INTO Preisgruppe ( Name, someTableID, UserPreisgruppe_ID ) VALUES ( 'Gast',     @someTableID, 1)
INSERT INTO Oeffnungszeit ( someTableID, Tag_ID, von,bis) VALUES ( @someTableID, 0,     '00:00','00:00'),( @someTableID, 1, '00:00','00:00'),( @someTableID, 2, '00:00','00:00'),(     @someTableID, 3, '00:00','00:00'),( @someTableID, 4, '00:00','00:00'),( @someTableID, 5,     '00:00','00:00'),( @someTableID, 6, '00:00','00:00')
GO

推荐答案

仅提供另一个示例，这是我使用的...

Just to provide another example, here is what I use...

表的定义:
这只是一个普通表，只是历史记录"表中有审核"字段的主体".

TABLE DEFINITION:
This is just a normal table except the "main body" of AUDIT fields are in the HISTORY table.

CREATE TABLE [data].[Categories](
    [Id] [uniqueidentifier] NOT NULL DEFAULT (newid()),
    [Name] [nvarchar](250) NOT NULL,
    [Description] [nvarchar](500) NULL,
    [DisplayOrder] [bigint] NULL,
    [ProductCount] [bigint] NULL,
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_Categories_IsActive]  DEFAULT ((1)),
    [UpdatedBy] [nvarchar](360) NOT NULL
)

在旁注...

• 不允许使用堆表，因此将每个"Id"列设为PRIMARY
• 您还应该习惯于将GUID用于主键

历史记录表定义(用于审核):
该表用于审核目的.您仍然可以看到谁做了什么&除非现在，否则历史记录不会保存在您的主表中，也不会降低索引的速度.而且...除了纯粹的日志运输外，您还能获得真正的审计.

HISTORY TABLE DEFINITION (used for audit purposes):
This table is used for AUDIT purposes. You still get to see who did what & when, except now, the history isn't buried in your main table and won't slow-down your INDEXES. And...you get TRUE AUDIT beyond that of mere log-shipping.

CREATE TABLE [history].[data_Categories](
    [Id] [uniqueidentifier] NOT NULL DEFAULT (newid()),
    [EntityId] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](250) NOT NULL,
    [Description] [nvarchar](500) NULL,
    [ProductCount] [bigint] NULL,
    [DisplayOrder] [bigint] NULL,
    [IsActive] [bit] NOT NULL,
    [UpdatedBy] [nvarchar](360) NOT NULL,
    [UpdateType] [nvarchar](50) NOT NULL,
    [UpdatedDate] [datetime] NOT NULL
)

GO

ALTER TABLE [history].[data_Categories] ADD  CONSTRAINT [DF_data_Categories_31EC6D26]  DEFAULT (newid()) FOR [Id]
GO

ALTER TABLE [history].[data_Categories] ADD  CONSTRAINT [DF_data_Categories_32E0915F]  DEFAULT (getutcdate()) FOR [UpdatedDate]
GO

ALTER TABLE [history].[data_Categories] ADD  DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [EntityId]
GO

在旁注...

• 您还可以在DELETE存储过程中关闭TRIGGERS，以使审核"更加干净
• 之所以变得干净"，是因为您获得了一条DELETE AUDIT记录，而不是UPDATE&删除审核记录
• 要执行此操作，只需在DELETE STATEMENT之前关闭TRIGGER，然后再将其重新打开即可.

表触发器:
只是一个普通的触发器...

TABLE TRIGGER:
Just a normal trigger...

CREATE TRIGGER [data].[trig_Categories] 
   ON  [data].[Categories]
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Id INT
    DECLARE @Type VARCHAR(20);

    IF EXISTS(SELECT * FROM INSERTED)
        BEGIN
            IF EXISTS(SELECT * FROM DELETED)
                BEGIN
                    SET @Type ='UPDATED';
                END
            ELSE
                BEGIN
                    SET @Type ='INSERTED';
                END


            INSERT INTO 
                history.data_Categories (
                    [EntityId]
                    ,[Name]
                    ,[Description]
                    ,[DisplayOrder]
                    ,[ProductCount]
                    ,[IsActive]
                    ,[UpdatedBy]
                    ,[UpdateType])
            SELECT 
                    [Id]
                    ,[Name]
                    ,[Description]
                    ,[DisplayOrder]
                    ,[ProductCount]
                    ,[IsActive]
                    ,[UpdatedBy]
                    ,@Type
            FROM INSERTED

        END
    ELSE
        BEGIN
            SET @type = 'DELETED';


            INSERT INTO 
                history.data_Categories (
                    [EntityId]
                    ,[Name]
                    ,[Description]
                    ,[DisplayOrder]
                    ,[ProductCount]
                    ,[IsActive]
                    ,[UpdatedBy]
                    ,[UpdateType])
            SELECT 
                    [Id]
                    ,[Name]
                    ,[Description]
                    ,[DisplayOrder]
                    ,[ProductCount]
                    ,[IsActive]
                    ,[UpdatedBy]
                    ,@Type          
            FROM DELETED
        END;
END

GO

这篇关于SQL Server输入触发器到天蓝色触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！