trigger with text type

Question

trigger with text type

tcronin 95651

SSCertifiable

Points: 6735
More actions
August 14, 2014 at 9:13 am

#397203

Dealing with 3rd party app that is somehow deleting records. Was going to put a delete trigger on the table so we can at least be notified of the delete and save the record. Great idea until I found out the table has a text data type for one of the fields which are'nt allowed on the delete or update portion of the trigger. Unfortunately the text data contains what we really need to retain. Tried doing a convert varchar and that would not work. Any idea for a workaround (ugly works for me).

Viewing 8 posts - 1 through 7 (of 7 total)

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

richardkeithmoss Old Hand Points: 398 More actions · Answer 1

How about taking snapshots of the table into another table and flagging the records that get deleted via a Merge statement?

I'm not sure how often you would need to take a snapshot, but as a start just doing it once a day could help pin things down.

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

Why don't you just change your datatype to varchar(max)? It should change seemlessly and avoids all the pain associated with using the deprecated text datatype.

_______________________________________________________________

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/

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 3

sorry but that is the real world, that's why I referenced 3rd party app, which rhymes with 3rd party c&#*#rap. Cant change

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

tcronin 95651 (8/14/2014)
sorry but that is the real world, that's why I referenced 3rd party app, which rhymes with 3rd party c&#*#rap. Cant change

LOL. Why can't you change the datatype? It would be totally invisible to any 3rd party craptastic app. Any CRUD operations will deal with the change automatically.

_______________________________________________________________

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/

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Sean Lange (8/14/2014)
tcronin 95651 (8/14/2014)
sorry but that is the real world, that's why I referenced 3rd party app, which rhymes with 3rd party c&#*#rap. Cant change
LOL. Why can't you change the datatype? It would be totally invisible to any 3rd party craptastic app. Any CRUD operations will deal with the change automatically.

Not necessarily. The app could be using text-pointers or the "write" keyword.

--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)

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 6

tcronin 95651 (8/14/2014)
Dealing with 3rd party app that is somehow deleting records. Was going to put a delete trigger on the table so we can at least be notified of the delete and save the record. Great idea until I found out the table has a text data type for one of the fields which are'nt allowed on the delete or update portion of the trigger. Unfortunately the text data contains what we really need to retain. Tried doing a convert varchar and that would not work. Any idea for a workaround (ugly works for me).

This should not be a problem, here is an example

😎

Create table with text data type and an audit table

USE tempdb;

GO

CREATE TABLE dbo.TXTBL

(

TXTBL_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL

,TXTBL_TEXT TEXT NOT NULL

);

CREATE TABLE dbo.TXTBL_AUDIT

(

TXTBL_ID INT NOT NULL

,TXTBL_TEXT TEXT NOT NULL

);

Create a trigger

USE tempdb;

GO

CREATE TRIGGER TRG_DBO_TXTBL_INST_DEL

ON dbo.TXTBL

INSTEAD OF DELETE

AS

BEGIN

INSERT INTO dbo.TXTBL_AUDIT (TXTBL_ID,TXTBL_TEXT)

SELECT TXTBL_ID,TXTBL_TEXT FROM deleted;

DELETE FROM dbo.TXTBL

WHERE TXTBL_ID IN (SELECT TXTBL_ID FROM deleted);

END

Insert some records

INSERT INTO dbo.TXTBL(TXTBL_TEXT) VALUES ('THIS IS SOME TEXT');

INSERT INTO dbo.TXTBL(TXTBL_TEXT) VALUES ('THIS IS SOME TEXT 2');

INSERT INTO dbo.TXTBL(TXTBL_TEXT) VALUES ('THIS IS SOME TEXT 3');

INSERT INTO dbo.TXTBL(TXTBL_TEXT) VALUES ('THIS IS SOME TEXT 4');

Delete from the table

USE tempdb;

GO

DELETE FROM dbo.TXTBL WHERE TXTBL_ID > 2;

Check the audit table

USE tempdb;

GO

SELECT * FROM dbo.TXTBL_AUDIT;

Results

TXTBL_ID TXTBL_TEXT

----------- --------------------

3 THIS IS SOME TEXT 3

4 THIS IS SOME TEXT 4

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 7

patient clinical data can't make the change void the warranty , worse thing is an upgrade could blow up with any of these modification