SQL Trigger cannot do INSTEAD OF DELETE but is required for ntext, image columns

Question

CREATE TRIGGER [dbo].[C1_Deletions] ON [dbo].[C1] INSTEAD OF DELETE AS
SET NOCOUNT ON
  IF EXISTS ( SELECT 'True' FROM deleted JOIN C1 ON deleted.ACCOUNTNO = C1.ACCOUNTNO )
   BEGIN
    INSERT INTO [GDeletions].[dbo].[C1] SELECT * FROM deleted
    DELETE C1 FROM C1 INNER JOIN DELETED ON C1.ACCOUNTNO = DELETED.ACCOUNTNO
   END

So that is the trigger i'm trying to use, it works well when i'm deleting by accountno, but when i need to delete by recid(another column) i'm unable to.

If i change the INSTEAD OF to AFTER, i get errors about ntext, image columns not being allowed. Is there any way around this issue? I cannot be the one specifying the deletion string, the program itself does that i just need the trigger to grab the data that is being deleted.

The bigger problem i have is another table that stores history, it stored it with the accountno matching to the c1 table but then there is also recid which is unique to every entry. If i go to delete a C1 entry, it deletes all from history using accountno, but if i delete a single history entry then it deletes by recid.

Answer 1

You cannot access TEXT, NTEXT or IMAGE fields from INSERTED or DELETED. However you can access them from the base table by joining with INSERTED. This only works for INSERT and UPDATE because in a DELETE the base row no longer exists.

To achieve what you need, in another trigger, copy the primary key and TEXT, NTEXT and IMAGE columns to a side table.

For example

create table C1(
   accountNo int identity primary key,
   someColumn nvarchar(10),
   someNtext ntext
)

create table C1_side(
   accountNo int primary key,
   someNtext ntext
)

create trigger trgC1_IU  on C1 AFTER INSERT, UPDATE
as
BEGIN
   -- Ensure side row exists
   insert C1_side(accountNo, someNtext)
   select accountNo from INSERTEd
   where not exists (select 1 from C1_side where C1_side.accountNo = inserted.accountNo)

   -- Copy NTEXT value to side row
   update C1_side
   set someNtext = c1.someNtext
   from C1_side inner join C1 on C1_side.accountNo = C1.accountNo
   inner join INSERTED on INSERTED.accountNo = C1.accountNo
   -- Could improve by checking if the column was updated for efficiency

END

Now, in your DELETE trigger, you can join DELETED to C1_side to read the previous value of the ntext column. Note that you will have to populate initial values for your side table, for rows which already exist in C1.

Answer 2

Stop using ntext and image: they are deprecated. Use nvarchar(max) and varbinary(max) instead. These act like normal datatypes, unlike the deprecated ones.