We help IT Professionals succeed at work.
Get Started

How to create a dynamic Triger to Multiple Update?

262 Views
Last Modified: 2012-08-14
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.
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE