ERROR WHILE CREATING TRIGGER - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ERROR WHILE CREATING TRIGGER

Author

Topic

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2012-04-19 : 01:00:31

Hi All,

I have a issue while creating the update trigger for my audit table.

The table script is as below :

CREATE TABLE [dbo].[PNET_LEAVE_LEAVE](
[LEAVE_ID] [int] IDENTITY(1,1) NOT NULL,
[EMP_ID] [int] NOT NULL,
[ORG_ID] [int] NOT NULL,
[LEAVE_APPDATE] [datetime] NOT NULL,
[LEAVETYPE_ID] [int] NOT NULL,
[LEAVE_FROM] [date] NOT NULL,
[LEAVE_TO] [date] NOT NULL,
[LEAVE_STATUSID] [int] NOT NULL,
[DURATION_STARTDATE] [bit] NOT NULL,
[DURATION_ENDDATE] [bit] NOT NULL,
[LEAVE_ADDRESS] [varchar](250) NOT NULL,
[LEAVE_CONTACTNO] [varchar](20) NOT NULL,
[LEAVE_CANCELREASON] [varchar](250) NULL,
[MGR_EMPID] [int] NULL,
[MGR_COMMENTS] [varchar](250) NULL,
[MGR_DATETIME] [datetime] NULL,
[ESCALATION] [bit] NULL,
[ALTERNATE_APPROVAR] [bit] NULL,
[INTERVENE] [bit] NULL,
[INTERVENE_DATE] [datetime] NULL,
[INFORMED_HR] [bit] NULL,
[INTERVENE_MGR_ID] [int] NULL,
[INTERVENE_MGR_COMMENTS] [varchar](250) NULL,
[NO_DAYS] [numeric](4, 2) NOT NULL,
[MONTH] [int] NOT NULL,
[YEAR] [int] NOT NULL,
[LEAVE_REASON] [varchar](250) NOT NULL,
[DEL_FLAG] [bit] NULL,
[DEL_TIME] [datetime] NULL,
[USER_ID] [varchar](256) NULL,
[SHORT_REASON] [varchar](20) NULL,
[DEPARTMENTID] [int] NOT NULL,
[DataVersion] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [nvarchar](200) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](200) NOT NULL,
[MODIFIED_USERID] [varchar](256) NULL,
[IPADDRESS] [varchar](20) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] WITH CHECK ADD FOREIGN KEY([DEPARTMENTID])
REFERENCES [dbo].[PNET_MASTER_FUNCTION_DEPARTMENT] ([FUNCTION_DEPARTMENTID])
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] ADD CONSTRAINT [DF_PNET_LEAVE_LEAVE_DURATION_STARTDATE] DEFAULT ((1)) FOR [DURATION_STARTDATE]
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] ADD CONSTRAINT [DF_PNET_LEAVE_LEAVE_DURATION_ENDDATE] DEFAULT ((1)) FOR [DURATION_ENDDATE]
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] ADD CONSTRAINT [DF_PNET_LEAVE_LEAVE_LEAVE_ADDRESS] DEFAULT ('N/A') FOR [LEAVE_ADDRESS]
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] ADD CONSTRAINT [DF_PNET_LEAVE_LEAVE_LEAVE_CONTACTNO] DEFAULT ('N/A') FOR [LEAVE_CONTACTNO]
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] ADD CONSTRAINT [DF_PNET_LEAVE_LEAVE_NO_DAYS] DEFAULT ((0)) FOR [NO_DAYS]
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] ADD CONSTRAINT [DF_PNET_LEAVE_LEAVE_DEL_FLAG] DEFAULT ((0)) FOR [DEL_FLAG]
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] ADD DEFAULT ((1)) FOR [DataVersion]
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] ADD DEFAULT (getdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] ADD DEFAULT (suser_name()) FOR [CreatedBy]
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] ADD DEFAULT (getdate()) FOR [ModifiedDate]
GO

ALTER TABLE [dbo].[PNET_LEAVE_LEAVE] ADD DEFAULT (suser_name()) FOR [ModifiedBy]
GO

The script for audit table is as below:

CREATE TABLE [dbo].[PNET_LEAVE_LEAVE_AUDIT](
[LEAVE_ID] [int] NOT NULL,
[EMP_ID] [int] NOT NULL,
[ORG_ID] [int] NOT NULL,
[LEAVE_APPDATE] [datetime] NOT NULL,
[LEAVETYPE_ID] [int] NOT NULL,
[LEAVE_FROM] [date] NOT NULL,
[LEAVE_TO] [date] NOT NULL,
[LEAVE_STATUSID] [int] NOT NULL,
[DURATION_STARTDATE] [bit] NOT NULL,
[DURATION_ENDDATE] [bit] NOT NULL,
[LEAVE_ADDRESS] [varchar](250) NOT NULL,
[LEAVE_CONTACTNO] [varchar](20) NOT NULL,
[LEAVE_CANCELREASON] [varchar](250) NULL,
[MGR_EMPID] [int] NULL,
[MGR_COMMENTS] [varchar](250) NULL,
[MGR_DATETIME] [datetime] NULL,
[ESCALATION] [bit] NULL,
[ALTERNATE_APPROVAR] [bit] NULL,
[INTERVENE] [bit] NULL,
[INTERVENE_DATE] [datetime] NULL,
[INFORMED_HR] [bit] NULL,
[INTERVENE_MGR_ID] [int] NULL,
[INTERVENE_MGR_COMMENTS] [varchar](250) NULL,
[NO_DAYS] [numeric](4, 2) NOT NULL,
[MONTH] [int] NOT NULL,
[YEAR] [int] NOT NULL,
[LEAVE_REASON] [varchar](250) NOT NULL,
[DEL_FLAG] [bit] NULL,
[DEL_TIME] [datetime] NULL,
[USER_ID] [varchar](256) NULL,
[SHORT_REASON] [varchar](20) NULL,
[DEPARTMENTID] [int] NOT NULL,
[DataVersion] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [nvarchar](200) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](200) NOT NULL,
[MODIFIED_USERID] [varchar](256) NULL,
[IPADDRESS] [varchar](20) NULL,
[ISDELETED] [bit] NULL
)

And the script for update trigger is as below:

DECLARE
@TableName varchar(100) = 'PNET_LEAVE_LEAVE',
@AuditTableName varchar(200)

SET @AuditTableName = @TableName + '_audit'

DECLARE @SQL nvarchar(max) = ''
IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Update')
BEGIN
SET @SQL = @SQL + '
CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Update] ON ' + @TableName + '
FOR UPDATE
AS

SET NOCOUNT ON

UPDATE a SET
a.DataVersion = b.DataVersion + 1,
a.ModifiedDate = GETDATE(),
a.ModifiedBy = SYSTEM_USER,
FROM ' + @TableName + ' a
INNER JOIN inserted b
ON '

SELECT @SQL = @SQL + 'a.' + c.name + ' = b.' + c.name + ' AND '
FROM sys.index_columns sc
JOIN sys.columns c
ON sc.object_id = c.object_id
AND sc.column_id = c.column_id
JOIN sys.indexes i
ON sc.object_id = i.object_id
AND sc.index_id = i.index_id
WHERE OBJECT_NAME(sc.object_id) = @TableName
AND i.is_primary_key = 1
ORDER BY index_column_id ASC

SET @SQL = LEFT(@SQL, LEN(@SQL) - 4) + CHAR(13) + CHAR(13)

SET @SQL = @SQL + ' INSERT INTO ' + @AuditTableName + '
('

SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ')
SELECT '

SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ' = 0
FROM deleted

GO'

PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END

The error is as below:

CREATE TRIGGER [trgPNET_LEAVE_LEAVEUpdate] ON PNET_LEAVE_LEAVE
FOR UPDATE
AS

SET NOCOUNT ON

UPDATE a SET
a.DataVersion = b.DataVersion + 1,
a.ModifiedDate = GETDATE(),
a.ModifiedBy = SYSTEM_USER,
FROM PNET_LEAVE_LEAVE a
INNER JOIN inserted b

INSERT INTO PNET_LEAVE_LEAVE_audit
([LEAVE_ID], [EMP_ID], [ORG_ID], [LEAVE_APPDATE], [LEAVETYPE_ID], [LEAVE_FROM], [LEAVE_TO], [LEAVE_STATUSID], [DURATION_STARTDATE], [DURATION_ENDDATE], [LEAVE_ADDRESS], [LEAVE_CONTACTNO], [LEAVE_CANCELREASON], [MGR_EMPID], [MGR_COMMENTS], [MGR_DATETIME], [ESCALATION], [ALTERNATE_APPROVAR], [INTERVENE], [INTERVENE_DATE], [INFORMED_HR], [INTERVENE_MGR_ID], [INTERVENE_MGR_COMMENTS], [NO_DAYS], [MONTH], [YEAR], [LEAVE_REASON], [DEL_FLAG], [DEL_TIME], [USER_ID], [SHORT_REASON], [DEPARTMENTID], [DataVersion], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [MODIFIED_USERID], [IPADDRESS], [ISDELETED])
SELECT [LEAVE_ID], [EMP_ID], [ORG_ID], [LEAVE_APPDATE], [LEAVETYPE_ID], [LEAVE_FROM], [LEAVE_TO], [LEAVE_STATUSID], [DURATION_STARTDATE], [DURATION_ENDDATE], [LEAVE_ADDRESS], [LEAVE_CONTACTNO], [LEAVE_CANCELREASON], [MGR_EMPID], [MGR_COMMENTS], [MGR_DATETIME], [ESCALATION], [ALTERNATE_APPROVAR], [INTERVENE], [INTERVENE_DATE], [INFORMED_HR], [INTERVENE_MGR_ID], [INTERVENE_MGR_COMMENTS], [NO_DAYS], [MONTH], [YEAR], [LEAVE_REASON], [DEL_FLAG], [DEL_TIME], [USER_ID], [SHORT_REASON], [DEPARTMENTID], [DataVersion], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [MODIFIED_USERID], [IPADDRESS], [ISDELETED] = 0
FROM deleted

GO
Msg 156, Level 15, State 1, Procedure trgPNET_LEAVE_LEAVEUpdate, Line 12
Incorrect syntax near the keyword 'FROM'.

can any one help me where the error is.

Regards
Kiran Murali

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 01:17:34

you've an extra comma in update list


...

UPDATE a SET 
a.DataVersion = b.DataVersion + 1,
a.ModifiedDate = GETDATE(),
a.ModifiedBy = SYSTEM_USER,
FROM PNET_LEAVE_LEAVE a
INNER JOIN inserted b
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2012-04-19 : 02:48:17

I have removed that extra comma, its giving the following error :

Msg 156, Level 15, State 1, Procedure trgPNET_LEAVE_LEAVEUpdate, Line 14
Incorrect syntax near the keyword 'INSERT'.
Msg 102, Level 15, State 1, Procedure trgPNET_LEAVE_LEAVEUpdate, Line 15
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 08:50:27

you're missing a join condition in last update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources