Link to home
Start Free TrialLog in
Avatar of Wisdown
WisdownFlag for Brazil

asked on

How to create a dynamic Triger to Multiple Update?

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
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Wisdown

ASKER

Thank you very much, now works pefectly!!!!
Avatar of Wisdown

ASKER

With those triggers everyone can have a good Audit System, and export data to other servers keeping the integrity between the databases.
Thank you very much appari for the fast answer!!!!