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
Ask a Question
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

troubleshooting Question

Creating a ChangeLog Database on SQL Server 2005

Avatar of Carla Romere
Carla RomereFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
2 Comments1 Solution652 ViewsLast Modified:
I am following a script I got at a SQL conference that creates a ChangeLog table and then creates a trigger that fires on any creation, modification, or deletion of database objects. I ran the following script and the table was created successfully. However, it is failing on the trigger creation.
/****** Object: Table [dbo].[ChangeLog] ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE TABLE [dbo].[aer_ChangeLog](
[LogId] [bigint] IDENTITY(1,1) NOT NULL,
[DatabaseName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL DEFAULT (getutcdate()),
[LoginName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY NONCLUSTERED
	(
		[LogId] ASC
	)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF


--Trigger
/****** Object: DdlTrigger [ddltrg_ObjectRevisionHistory] ******/
GO
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [aer_ddltrg_ObjectRevisionHistory]
ON [fin_pilot]
FOR
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_INDEX, ALTER_INDEX, DROP_INDEX, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER, CREATE_STATISTICS, DROP_STATISTICS, UPDATE_STATISTICS, CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA, GRANT_DATABASE, DENY_DATABASE, REVOKE_DATABASE, CREATE_USER, ALTER_USER, DROP_USER, CREATE_VIEW, ALTER_VIEW, DROP_VIEW
as
BEGIN
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO dbo.aer_ChangeLog
(databasename, eventtype, objectname, objecttype, sqlcommand, loginname)
VALUES
(
	@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
	@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
	@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
	@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
	@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
	@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
END


GO
SET
ANSI_NULLS OFF
GO
SET
QUOTED_IDENTIFIER OFF
GO
ENABLE
TRIGGER [aer_ddltrg_ObjectRevisionHistory] ON fin_pilot
The error that I receive on the trigger creation is:
Msg 1098, Level 15, State 1, Procedure aer_ddltrg_ObjectRevisionHistory, Line 21
The specified event type(s) is/are not valid on the specified target object.
I looked up the EVENTDATA() data types for sql server 2005 and the data types appear to be valid. The error is actually showing to be on the line containing the closing parentheses after the @data.value lines.

What is causing this error?
ASKER CERTIFIED SOLUTION
Avatar of Surendra Nath
Surendra NathFlag of India imageTechnology Lead

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

Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answers