troubleshooting Question

How to create a dynamic Triger to Multiple Update?

Avatar of Wisdown
WisdownFlag for Brazil asked on
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft SQL Server 2008
3 Comments1 Solution263 ViewsLast Modified:
Hey Guys,

The first thing i need say its my english isnt good. So i hope you can understand what i`m trying say.

I need do 2 kind of triggers for UPDATE, 2 for INSERT and 2 for DELETE, because the ERP on enterprise where i work, dont allow i make index on tables... so my idea (to first trigger) its copy the ERP data for a secondary table in same server for intranet and for another server to web, for last (the second trigger) i need audit the changes on Database.

Here a Database script for example:

/*
**********************************************************************************************************
*/

USE [DBA]
GO
/****** Object:  Table [dbo].[TABELA]    Script Date: 03/19/2009 04:06:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TABELA]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TABELA](
      [CAMPOID] [int] IDENTITY(1,1) NOT NULL,
      [CAMPO1] [nvarchar](50) NULL,
      [CAMPO2] [nvarchar](50) NULL,
 CONSTRAINT [PK_TABELA] PRIMARY KEY CLUSTERED
(
      [CAMPOID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[AUDIT]    Script Date: 03/19/2009 04:06:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AUDIT]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AUDIT](
      [Data] [smalldatetime] NOT NULL,
      [Aplicativo] [nvarchar](255) NOT NULL,
      [UsuarioSQL] [nvarchar](255) NOT NULL,
      [UsuarioSistema] [nvarchar](255) NOT NULL,
      [Computador] [nvarchar](255) NOT NULL,
      [TipoAtualizacao] [char](1) NOT NULL,
      [CAMPOID] [int] NULL,
      [CAMPO1] [nvarchar](50) NULL,
      [CAMPO2] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TABELA_CUSTOM]    Script Date: 03/19/2009 04:06:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TABELA_CUSTOM]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TABELA_CUSTOM](
      [CAMPOID] [int] NOT NULL,
      [CAMPO1] [int] NULL,
      [CAMPO2] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO
/****** Object:  Trigger [TABELALogUpdateCUSTOM]    Script Date: 03/19/2009 04:06:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TABELALogUpdateCUSTOM]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER
                        [dbo].[TABELALogUpdateCUSTOM]
            ON
                  [dbo].[TABELA]

                  AFTER
                        UPDATE

                  AS

                        SET NOCOUNT ON


                        IF
                              ((SELECT CAMPO1 FROM INSERTED) <> (SELECT CAMPO1 FROM DELETED)) OR
                              ((SELECT CAMPO2 FROM INSERTED) <> (SELECT CAMPO2 FROM DELETED))

                        BEGIN

                              UPDATE
                                    TC
                              SET
                                    TC.CAMPO1 = T.CAMPO1,
                                    TC.CAMPO2 = T.CAMPO2
                              FROM
                                    TABELA_CUSTOM TC
                              INNER JOIN
                                    TABELA T
                              ON
                                    TC.CAMPOID = T.CAMPOID
                              


                        END'
GO
/****** Object:  Trigger [TABELALogUpdate]    Script Date: 03/19/2009 04:06:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TABELALogUpdate]'))
EXEC dbo.sp_executesql @statement = N'
            CREATE TRIGGER
                        [dbo].[TABELALogUpdate]
            ON
                  [DBA].[dbo].[TABELA]

                  AFTER
                        UPDATE

                  AS

                        SET NOCOUNT ON;

                        IF
                              ((SELECT CAMPO1 FROM INSERTED) <> (SELECT CAMPO1 FROM DELETED)) OR
                              ((SELECT CAMPO2 FROM INSERTED) <> (SELECT CAMPO2 FROM DELETED))

                              BEGIN

                                    INSERT INTO
                                          [DBA].[dbo].[AUDIT]
                                          (Data, Aplicativo, UsuarioSQL, UsuarioSistema, Computador, TipoAtualizacao, CAMPOID, CAMPO1, CAMPO2)

                                    SELECT
                                          GETDATE(),
                                          APP_NAME(),
                                          USER_NAME(),
                                          SYSTEM_USER,
                                          HOST_NAME(),
                                          ''U'',
                                          T.CAMPOID,
                                          T.CAMPO1,
                                          T.CAMPO2
                                    FROM
                                          TABELA T
                                    INNER JOIN
                                          INSERTED I
                                    ON
                                          T.CAMPOID = I.CAMPOID
                                    INNER JOIN
                                          DELETED D
                                    ON
                                          I.CAMPOID = D.CAMPOID

                              END'
GO
/****** Object:  Trigger [TABELALogInsertCUSTOM]    Script Date: 03/19/2009 04:06:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TABELALogInsertCUSTOM]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER
                  [dbo].[TABELALogInsertCUSTOM]
            ON
                  [dbo].[TABELA]

                  FOR
                        INSERT

                  AS

                        SET NOCOUNT ON

                        INSERT INTO
                              [DBA].[dbo].[TABELA_CUSTOM]
                              (CAMPOID, CAMPO1, CAMPO2)

                        SELECT
                              CAMPOID,
                              CAST (CAMPO1 AS INT),
                              CAMPO2
                        FROM
                              INSERTED
'
GO
/****** Object:  Trigger [TABELALogInsert]    Script Date: 03/19/2009 04:06:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TABELALogInsert]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER
                  [dbo].[TABELALogInsert]
            ON
                  [dbo].[TABELA]

                  FOR
                        INSERT

                  AS

                        SET NOCOUNT ON

                        INSERT INTO
                              [DBA].[dbo].[AUDIT]
                              (Data, Aplicativo, UsuarioSQL, UsuarioSistema, Computador, TipoAtualizacao, CAMPOID, CAMPO1, CAMPO2)

                        SELECT
                              GetDate(),
                              App_Name(),
                              User_Name(),
                              System_User,
                              Host_Name(),
                              ''I'',
                              CAMPOID,
                              CAMPO1,
                              CAMPO2
                        FROM
                              INSERTED'
GO
/****** Object:  Trigger [TABELALogDeleteCUSTOM]    Script Date: 03/19/2009 04:06:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TABELALogDeleteCUSTOM]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER
                  [dbo].[TABELALogDeleteCUSTOM]
            ON
                  [dbo].[TABELA]

                  FOR
                        DELETE

                  AS

                        SET NOCOUNT ON

                        DELETE FROM
                              [DBA].[dbo].[TABELA_CUSTOM]
                        WHERE
                              [CAMPOID] = (SELECT CAMPOID FROM DELETED)'
GO
/****** Object:  Trigger [TABELALogDelete]    Script Date: 03/19/2009 04:06:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TABELALogDelete]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER
                  [dbo].[TABELALogDelete]
            ON
                  [dbo].[TABELA]

                  FOR
                        DELETE

                  AS

                        SET NOCOUNT ON

                        INSERT INTO
                              [DBA].[dbo].[AUDIT]
                              (Data, Aplicativo, UsuarioSQL, UsuarioSistema, Computador, TipoAtualizacao, CAMPOID, CAMPO1, CAMPO2)

                        SELECT
                              GetDate(),
                              App_Name(),
                              User_Name(),
                              System_User,
                              Host_Name(),
                              ''D'',
                              [CAMPOID],
                              [CAMPO1],
                              [CAMPO2]
                        FROM
                              DELETED
'
GO

/*
**********************************************************************************************************
*/

The main problem its when multiple updates/delete occours, so i get the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The problem i need make those triggers dynamic since the ERP can change anytime his tables.
Other thing, i`m trying filter the data on update to prevent the AUDIT become so big, because the ERP ALWAYS UPDATE the tables with same data when any user request data...
Any idea for help me set a solution in this scenario? Like as Trigger with Cursor or DLL?

Thanks in advice.
ASKER CERTIFIED SOLUTION
appari

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros