SQL Server input trigger to azure trigger

Question

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

Answer 1

Nothing is looking bad in this Trigger. I did try your code and it's working fine. So it could be the structure. My look like this:

CREATE TABLE [dbo].[someTable](
    [someTableID] [int] IDENTITY(1,1) NOT NULL,
    [Column1] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[Preisgruppe](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [someTableID] [int] NULL,
    [UserPreisgruppe_ID] [int] NULL
)

CREATE TABLE [dbo].[Oeffnungszeit](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [someTableID] [int] NOT NULL,
    [Tag_ID] [int] NOT NULL,
    [von] [time](7) NULL,
    [bis] [time](7) NULL
)

Also it could be nice to have the Error message...

Answer 2

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
)

On a side-note...

• Heap tables are not allowed, so make each "Id" columns PRIMARY
• You should also get used to using GUID's for your PRIMARY KEY's

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

On a side-note...

• You can also turn-off TRIGGERS in your DELETE stored procedures to make the AUDIT "cleaner"
• The reason it becomes "cleaner" is you get a single DELETE AUDIT record instead of an UPDATE & DELETE AUDIT record
• To do this, just turn off the TRIGGER before the DELETE STATEMENT and turn it on again afterwards.

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