December 16, 2009 at 9:54 am
Hi I am trying to write a Instead of trigger on a table with image column, we have a obligation that we cannot use varbinary(max) at this moment. This trigger what I have written is auditing what all is needed , but not updating the actual table when running the update on this image column.
Could some one help me in this
CREATE TABLE [dbo].[luOnlineImage](
[Value] [varchar](30) NOT NULL,
[ImageWidth] [int] NULL,
[ImageHeight] [int] NULL,
[Image] [image] NULL,
[Code] [int] IDENTITY(1000,1) NOT NULL,
[Description] [varchar](250) NULL,
[Sequence] [int] NULL,
[IsDisabled] [char](1) NOT NULL,
[IsDefault] [char](1) NOT NULL,
[RecordingUserName] [varchar](25) NOT NULL,
[RecordingTime] [datetime] NOT NULL,
[RowVersion] [int] NOT NULL,
CONSTRAINT [PK_luOnlineImage] PRIMARY KEY CLUSTERED
(
[Code] 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]
CREATE TABLE [dbo].[z_luOnlineImage](
[ActionDML] [char](1) NULL,
[Code] [int] NOT NULL,
[RowVersion] [int] NOT NULL,
[Value] [varchar](30) NOT NULL,
[ImageWidth] [int] NULL,
[ImageHeight] [int] NULL,
[Description] [varchar](250) NULL,
[Sequence] [int] NULL,
[IsDisabled] [char](1) NOT NULL,
[IsDefault] [char](1) NOT NULL,
[RecordingUserName] [varchar](25) NOT NULL,
[RecordingTime] [datetime] NOT NULL,
CONSTRAINT [PK_z_luOnlineImage] PRIMARY KEY CLUSTERED
(
[Code] ASC,
[RowVersion] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--Trigger
Create TRIGGER [TR_UD_luOnlineImage] ON luOnlineImage
INSTEAD OF DELETE, UPDATE
AS
DECLARE @ErrorMessage VARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
BEGIN TRY
INSERT INTO z_luOnlineImage
([ActionDML] ,[Code] , [RowVersion] , [Value] , [ImageWidth] , [ImageHeight] , [Description] , [Sequence] , [IsDisabled] , [IsDefault] , [RecordingUserName] , [RecordingTime])
SELECT CASE WHEN deleted.Code IS NOT NULL AND inserted.Code IS NOT NULL
THEN CASE WHEN deleted.ROWVERSION = -1 THEN 'M'
WHEN deleted.ROWVERSION = 0 THEN 'I'
ELSE 'U'
END
WHEN deleted.Code IS NOT NULL AND inserted.Code IS NULL
THEN 'D'
END as ActionDML,
Deleted.[Code] , Deleted.[RowVersion] , Deleted.[Value] , Deleted.[ImageWidth] , Deleted.[ImageHeight] , Deleted.[Description] , Deleted.[Sequence] , Deleted.[IsDisabled] , Deleted.[IsDefault] , Deleted.[RecordingUserName] , Deleted.[RecordingTime]
FROM deleted
LEFT JOIN luOnlineImage on luOnlineImage.Code = deleted.Code
LEFT JOIN inserted on luOnlineImage.Code = inserted.Code
BEGIN
IF (SELECT COUNT(*) FROM inserted) = 0
Delete luOnlineImage where Code in (select Code from deleted)
ELSE
UPDATE luOnlineImage set [RowVersion] = inserted.[RowVersion]
, [Value] = inserted.[Value]
, [ImageWidth] = inserted.[ImageWidth]
, [ImageHeight] = inserted.[ImageHeight]
, [Description] = inserted.[Description]
, [Sequence] = inserted.[Sequence]
, [IsDisabled] = inserted.[IsDisabled]
, [IsDefault] = inserted.[IsDefault]
, [RecordingUserName] = inserted.[RecordingUserName]
, [RecordingTime] = inserted.[RecordingTime]
FROM luOnlineImage, inserted
WHERE luOnlineImage.Code = inserted.Code
END
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'TR_UD_luOnlineImage:'+ ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF @@TRANCOUNT > 0
BEGIN
RAISERROR (@ErrorMessage, 16,1) with nowait
ROLLBACK TRAN
RETURN
END
END CATCH
December 16, 2009 at 10:01 am
Very specifically, text, ntext, and image data types in the inserted and deleted tables in triggers don't work. Books Online and MSDN document this behavior.
The way to get around this is to move the logging into the insert/update/delete stored procedures, instead of having it in triggers. (Better is to use the "max" data types, but you already mentioned you can't do that.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply