Skip to Content

Msg: 2116, Level: 16, State: 1

CREATE TRIGGER failed because selecting from a TEXT, IMAGE, UNITEXT or off-row Java datatype column of the inserted or deleted table is not supported.

Explanation:

Starting with ASE 15.0.3 ESD 4, 15.5 ESD 2, and 15.7, ASE started raising error 2116 if the code for a CREATE TRIGGER attempted to reference a LOB (text, image, unitext) column from the trigger’s inserted or deleted  tables.  Such triggers could be created in earlier versions, but selecting text or image data from the inserted/deleted pseudotables in triggers has never actually been supported.   The documentation has always stated that it cannot be done although ASE hasn’t previously enforced the restriction; that the code works in some cases is accidental.  The main reason for starting to enforce the restriction came from misleading 10785 errors being raised when text/image was accessed using the new “INSTEAD OF” trigger feature against views  (CR 590800), but we realized the issue was generic to all triggers.

The core issue is that the inserted and deleted tables are essentially views of the data row change records from syslogs, but do not include the records containing the text/image values themselves.  The data returned for the text/image values does not come from the log because that access method was never designed or implemented;  rather ASE is just using the textptr value from the inserted  or deleted row in syslogs as it would for a regular table.  As a result it accesses the current contents of the page the textptr points to rather than the data for the LOB value in the log records..

That is why the following example returns “after” when it should return “before” – it is reading the text value from the data page accessed through the textptr (the textptr generally does not actually change when the value is updated, ASE reuses the existing allocations for the new value and allocates more pages if needed).

select @@version

go

create table t (x text)

go

create trigger utrig on t for update as

select x as “value_from_inserted” from inserted

select x as “value from_deleted” from deleted

go

insert t values (“before”)

go

update t set x = “after”

go

Adaptive Server Enterprise/15.0.3/EBF 16736 ESD#2/P/Sun_svr4/OS 5.8/ase1503/270 7/64-bit/FBO/Sun Jul 26 10:29:50 2009

value_from_inserted

———————————

after

(1 row affected)

value from_deleted

———————————

after

(1 row affected)

logically, this result should be “before” – but as described above the  text/image values aren’t supported.

Another example of a problem when selecting text/image from deleted is that
error 7128 is raised if the trigger was on DELETE.

create trigger deltrig on t for delete

as  select  x from deleted where x like ‘%’

go

delete t

go

Msg 7128, Level 16, State 3:

Server ‘bret_sun2’, Procedure ‘deltrig’, Line 7:

Text pointer value 0x0000036a000000000000000000001b18 references a data page which is no longer allocated. This text pointer references first text page 874, which belongs to object ID 1273872784.

x

———————————

(0 rows affected)

The supported way to get the new text/image value  is to select the value from the user table (rather than inserted) by joining with inserted on the primary key.

e.x. rather than

create trigger mytrigger on mytable for insert, update

as

insert text_archive select text_col from inserted

go

write the code as

create trigger mytrigger on mytable for insert, update

as

insert text_archive select m.text_col

from mytable m, inserted i

where m.primarykeycol = i.primarykeycol

go

There is no direct supported way to get the deleted or pre-update value, though user code can be written to explicitly archive the value before updating or deleting it.

There is an indirect method to access the old value that is supportable, but  it is cumbersome and doubles the amount of storage needed for the text/image values. That method is to create a shadow table that contains the primary key of the base table and a copy of the text/image value.  Initially populate this shadow table by inserting into it the current values in the base table.   Then on the base table, the triggers should contain logic to maintain the shadow table:

  • insert trigger: should insert the new values to the shadow table
  • delete trigger:  user code that needs to access the old value can get it from the shadow table referenced by the primary key.  The trigger should then finish up by deleting the row from the shadow table.
  • update trigger:  user code that needs to access the old value can get it from the shadow table referenced by the primary key.  The trigger should then finish up by updating the text/image values in the shadow table with the current value from the base table.

The enforcement of the restriction can be removed by booting ASE with traceflag 1716 (put -T1716 in the dataserver parameter list in the RUN_SERVER file).  This will give  the same behavior as pre-15.0.3 ESD 4 versions  (including the errors and incorrect results when selecting from deleted).

There is an open feature request 593730 to formally support text/image values in triggers.

One of my current projects is updating and expanding the ASE Troubleshooting and Error Messages Guide (TSG).  As I update existing error writeups and create new ones, I’ll be posting the new content to this blog rather than making you wait for the next edition of the TSG.  If there is an error message that you are particularly interested in seeing a writeup on, please feel free to send me a direct message giving the error number or text and any comments you may have on the message and I’ll plan to work on it sooner rather than later.  If your need for an explanation is urgent, please follow the usual process for opening a Technical Support case (SAP message) for assistance with your situation.

-bret

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Lisa Li

    I wrote the code as you suggested and still get the same error message.

    Not sure what happened. I wrote my trigger last year as the way you recommended, it worked. Now it won’t compile. My ASE is ASE 15.5 ESD#5.2.

    Any suggestions.

    (0) 
      1. Lisa Li

        Hi Bret,

        Thanks for your quick reply.

        Here is the error I got:

        Number (2116) Severity (16) State (1) Server x Procedure x CREATE TRIGGER failed because selecting from a TEXT, IMAGE, UNITEXT of off-row Java datatype column of the inserted or deleted table is not supported.

        (0) 
        1. Bret Halford Post author

          Hi Lisa,

          What I’m really looking for is the code you have written (or a similar simplification of it) that causes the error.

          -bret

          (0) 
          1. Lisa Li

            Sorry. Here is the place which causes the error:

            declare @textPointer varbinary(16)

            declare @iDescriptionId int

            select @iDescriptionId=@@identity

            –insert a record first

            exec db..P_InsTextComments @iDescriptionId, @textPointer

            –then update its TextComment column

            Update db..TextComments

            set TextComment=inserted.DESCRIPTION

            from inserted, db..TextComments a

            where a.TextCommentId=@iDescriptionId

            here is the TextComments DDL:

            Create table TextComments

            (TextCommentId int identity,

            TextComment text)

            (0) 
            1. Bret Halford Post author

              Hi Lisa,

              It looks like your trigger code is reading a text value from the inserted table on this line:

              set TextComment=inserted.DESCRIPTION

              The code needs to get the value from the table the trigger was created on rather than from the inserted table in the trigger.  You can use the inserted table to identify the primary key value of the row in the table.

              (0) 
              1. Lisa Li

                Bret,

                Thank you very much. Now I get it. Just cannot get any TEXT, IMAGE, or UNITEXT from the inserted or deleted. It used to work but need to rewrite now. However as you mentioned working was accidental.

                (0) 
  2. vishal chadha

    Are we really saying if you were using text columns in 12.5.4 using a trigger to create a shadow table

    we will not support in 15.X  Unless you make major changes to your application…..

    (0) 
    1. Bret Halford Post author

      I’m not sure if I understand your question.

      No changes in an application are necessary; if you cause the server to boot with the traceflag active, triggers on text will continue to function as they did before in 12.5.4.

      (0) 
  3. Pradeep Jangra

    Hi Bret, Thank you for this information.

    However this is useful only if we are planning to monitor inserts and updates.

    Do you have any approach for monitoring deletion..!!

    (0) 
    1. Bret Halford Post author

      I discuss deletion at the bottom of the writeup.  I don’t particularly like the shadow table approach, but it is the only solution I know of to access the text value in a delete trigger.

      (0) 

Leave a Reply