October 13, 2015 at 8:06 am
I would like to have a dynamic trigger on INSERT, UPDATE and DELETE. (each their own trigger).
What i want, is the trigger to log what has happened with the record. For the insert trigger, i would like to select all columns and put them in 1 column to my logging table. For the update trigger, i need the same, however for both of the old and new items.
For example:
CREATE TABLE [dbo].[TRIGGER_TEST](
[COLUMNA] [int] IDENTITY(1,1) NOT NULL,
[COLUMNB] [nvarchar](50) NOT NULL,
[COLUMNC] [nvarchar](20) NULL,
[COLUMND] [date] NULL,
[COLUME] [decimal](2, 2) NULL,
CONSTRAINT [PK_TRIGGER_TEST] PRIMARY KEY CLUSTERED
(
[COLUMNA] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
/****** Object: Trigger [dbo].[testTrigger] Script Date: 13-10-2015 15:58:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[testTrigger]
ON [dbo].[TRIGGER_TEST]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
SELECT *
FROM INSERTED
--insert all the items (dynamically) in a new table for logging purposes.
END
GO
CREATE TABLE [dbo].[TRIGGER_LOGGING](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OLD] [nvarchar](max) NULL,
[NEW] [nvarchar](max) NULL,
[PK] [int] NULL,
CONSTRAINT [PK_TRIGGER_LOGGING] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
When I insert an item in TRIGGER_TEST, i would like to see what is inserted in the TRIGGER_LOGGING table.
INSERT INTO [dbo].[TRIGGER_TEST]
([COLUMNB]
,[COLUMNC]
,[COLUMND]
,[COLUME])
VALUES
('test'
,'hello'
,getdate()
,0.1)
GO
I would like to see in my TRIGGER_LOGGING table in NEW: COLUMNB='test'|COLUMNC='hello'|COLUMND='2015-10-13 16:04'|COLUME=0,10
(or something like this 😉 )
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply