Trigger error using TEXT data type columns 
Author Message
 Trigger error using TEXT data type columns

Hi All,

I have a source table wherein some columns are "TEXT" data
type. I want to create a logging of data being updated or
deleted from this table. I have a mirror table containing
the same set of columns as what I have in my source table
except for TIMESTMP, USER_RESPONSIBLE, ACTION_TYPE columns
which I need for tracking.

I can't save my trigger and it returned this error:
    Error 311: Cannot use text, ntext, or image columns in
the 'inserted' and 'deleted' tables.

Here's my trigger:
    CREATE TRIGGER TRG_SAVE_RELATOR_LOG ON [dbo].
[TAGRelator]
    FOR UPDATE
    AS

BEGIN

   insert into pdbNOCRPT..tagrelatorlog
 (pckey,    
 create_time,    
 modify_time,    
 relator,
-- [description],
 rules,    
-- nodelist,    
-- alerts,    
-- escalation,    
 priority,    
 fire_once_only,    
 fire_once_every,    
 check_from_option,    
 agent,
 stop_escalation,    
 set_as_answered,    
 alert_when_stop_escalation,    
-- notify,    
 color,    
 custom_red,    
 custom_green,    
 custom_blue,    
-- calendar,    
 cycle,    
 testmode,    
 trace_flag,    
 check_not_available,    
 log_good,    
 run_now,    
-- check_rules,    
 usr_modify
 )  
 select  pckey,    
 create_time,    
 modify_time,    
 relator,
-- cast([description] as varchar(800)),
 rules,    
-- nodelist,    
-- alerts,    
-- escalation,    
 priority,    
 fire_once_only,    
 fire_once_every,    
 check_from_option,    
 agent,
 stop_escalation,    
 set_as_answered,    
 alert_when_stop_escalation,    
-- notify,    
 color,    
 custom_red,    
 custom_green,    
 custom_blue,    
-- calendar,    
 cycle,    
 testmode,    
 trace_flag,    
 check_not_available,    
 log_good,    
 run_now,    
-- check_rules,
 suser_sname()  from deleted

END

All lines with remarks are "TEXT" type columns. I can't do
this from within front-end level since the application
we're using is a package software. The possible way is
through backend only.
I tried using CAST and converting it to VARCHAR but I'm
still getting the same error. Do you know another
workaround? Hope you can help me.

Thanks in advance,
Ray



Fri, 05 May 2006 10:48:41 GMT
 Trigger error using TEXT data type columns

Quote:
>     Error 311: Cannot use text, ntext, or image columns in
> the 'inserted' and 'deleted' tables.

Yes, that limitation is known and documented. Perhaps you can change the logic so you can run it
inside an INSTEAD OF trigger?

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

Quote:

> Hi All,

> I have a source table wherein some columns are "TEXT" data
> type. I want to create a logging of data being updated or
> deleted from this table. I have a mirror table containing
> the same set of columns as what I have in my source table
> except for TIMESTMP, USER_RESPONSIBLE, ACTION_TYPE columns
> which I need for tracking.

> I can't save my trigger and it returned this error:
>     Error 311: Cannot use text, ntext, or image columns in
> the 'inserted' and 'deleted' tables.

> Here's my trigger:
>     CREATE TRIGGER TRG_SAVE_RELATOR_LOG ON [dbo].
> [TAGRelator]
>     FOR UPDATE
>     AS

> BEGIN

>    insert into pdbNOCRPT..tagrelatorlog
>  (pckey,
>  create_time,
>  modify_time,
>  relator,
> -- [description],
>  rules,
> -- nodelist,
> -- alerts,
> -- escalation,
>  priority,
>  fire_once_only,
>  fire_once_every,
>  check_from_option,
>  agent,
>  stop_escalation,
>  set_as_answered,
>  alert_when_stop_escalation,
> -- notify,
>  color,
>  custom_red,
>  custom_green,
>  custom_blue,
> -- calendar,
>  cycle,
>  testmode,
>  trace_flag,
>  check_not_available,
>  log_good,
>  run_now,
> -- check_rules,
>  usr_modify
>  )
>  select  pckey,
>  create_time,
>  modify_time,
>  relator,
> -- cast([description] as varchar(800)),
>  rules,
> -- nodelist,
> -- alerts,
> -- escalation,
>  priority,
>  fire_once_only,
>  fire_once_every,
>  check_from_option,
>  agent,
>  stop_escalation,
>  set_as_answered,
>  alert_when_stop_escalation,
> -- notify,
>  color,
>  custom_red,
>  custom_green,
>  custom_blue,
> -- calendar,
>  cycle,
>  testmode,
>  trace_flag,
>  check_not_available,
>  log_good,
>  run_now,
> -- check_rules,
>  suser_sname()  from deleted

> END

> All lines with remarks are "TEXT" type columns. I can't do
> this from within front-end level since the application
> we're using is a package software. The possible way is
> through backend only.
> I tried using CAST and converting it to VARCHAR but I'm
> still getting the same error. Do you know another
> workaround? Hope you can help me.

> Thanks in advance,
> Ray



Fri, 05 May 2006 11:59:30 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Data Transfer from Long data type to SQL Server Text data type column

2. Using the Text Data Type with a Trigger

3. type mismatch error while importing data to a BIT type column

4. Update Triggers on Text type column

5. Update Triggers on Text type column

6. Update Triggers on Text type column

7. Update Triggers on Text type column

8. Text Data Type and Triggers

9. Triggers and TEXT data type on insert.

10. SQL 6.5 problem with columns using TEXT field type

11. Access to Text Data-Type Fields inside Instead-Of-Triggers

12. text data type from deleted table (trigger)


 
Powered by phpBB® Forum Software