Trigger with a text datatype

Question

Trigger with a text datatype

PFlorenzano-641896

SSCommitted

Points: 1643
More actions
March 20, 2012 at 1:52 pm

#254995

Hello everyone,
I know you are unable to return a value within an AFTER trigger for a text datatype, however, folks are telling me this can be accomplished with a stored procedure.
My goal is to capture INSERTS, UPDATES and DELETES into an auditing table. Below is the code for my INSERT/UPDATE trigger:
USE [MAG]
GO
/****** Object: Trigger [dbo].[TR_TTGMMLCustomerNotesChangesInsertUpdate] Script Date: 03/20/2012 15:52:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[TR_TTGMMLCustomerNotesChangesInsertUpdate] ON [dbo].[TTGMMLCustomerNotes] FOR INSERT, UPDATE AS
/*[dbo].[TR_TTGMMLCustomerNotesChangesInsertUpdate]
Created By: Peter M. Florenzano
Date:03/20/2012
Purpose: This trigger is intended to ensure a complete Audit Trail of Inserts and Updates is logged
into the [dbo].[TTGMMLCustomerNotesAudit] table
*/
DECLARE @vcTriggerTypeVarchar(20)
SET @vcTriggerType = 'INSERTED'--Default to this
--Check if we are running for Insert or Update
IF EXISTS (SELECT * FROM DELETED)--Updates are basically Deletes and Inserts so they put a record in the deleted table
SET @vcTriggerType = 'Updated'
--Insert the Audit Record
INSERT INTO [dbo].[TTGMMLCustomerNotesAudit] (
[CustomerNoteKey],
[CUSTNMBR],
[NoteType],
--NoteText
[LastUpdateDate],
[LastUpdateBy],
[AuditReason],
[AuditUser],
[AuditDate])
SELECT
[CustomerNoteKey],
[CUSTNMBR],
[NoteType],
[LastUpdateDate],
[LastUpdateBy],
@vcTriggerType,
suser_sname(),
getdate()
FROMinserted
Thank you for any suggestions.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well as the error says, the INSERTED and DELETED tables will not let you use the text fields from those two special tables...so just get it from the base table itself. simply join inserted to teh base table based on the PK

so you need to insert the column from the original table instead.

alternatively, you could make it a two step process and update the audit table after the insert.

INSERT INTO [dbo].[TTGMMLCustomerNotesAudit] (

[CustomerNoteKey],

[CUSTNMBR],

[NoteType],

NoteText

[LastUpdateDate],

[LastUpdateBy],

[AuditReason],

[AuditUser],

[AuditDate])

SELECT

inserted.[CustomerNoteKey],

inserted.[CUSTNMBR],

inserted.[NoteType],

TTGMMLCustomerNotes.NoteText,

inserted.[LastUpdateDate],

inserted.[LastUpdateBy],

@vcTriggerType,

suser_sname(),

getdate()

FROM inserted

INNER JOIN TTGMMLCustomerNotes

ON inserted.[CustomerNoteKey]=TTGMMLCustomerNotes.[CustomerNoteKey]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Easiest solution is to stop using the text datatype. It is deprecated and will no longer be supported in the future. Instead you should use (n)varchar(MAX).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 3

Sean Lange (3/20/2012)
Easiest solution is to stop using the text datatype. It is deprecated and will no longer be supported in the future. Instead you should use (n)varchar(MAX).

I totally agree with you Sean, but I'm not allowed to alter the original table at this time. That NoteText filed contains characters over 50,000 in length. Can the (n)varchar(MAX) handle that?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

PFlorenzano-641896 (3/21/2012)
Sean Lange (3/20/2012)
Easiest solution is to stop using the text datatype. It is deprecated and will no longer be supported in the future. Instead you should use (n)varchar(MAX).
I totally agree with you Sean, but I'm not allowed to alter the original table at this time. That NoteText filed contains characters over 50,000 in length. Can the (n)varchar(MAX) handle that?

NVARCHAR(MAX) will handle the same number of characters as NTEXT. VARCHAR(MAX) will handle the same number of character as TEXT. It's all in Books Online.

...but I'm not allowed to alter the original table at this time.

I'll just bet there are no plans to do so, either. My recommendation is that they need to make it a priority and not just because NTEXT/TEXT is going away. The new MAX datatypes have a whole lot more capability/flexibility than NTEXT/TEXT does. Managment needs to realize that and make priority plans for the change, now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)