Text field in trigger

[ramsfield]

I am trying to track changes in a table via a trigger. I works fine except for a text (16) field. I get an error saying the text data type is invalid for local variables. How can make this work to include the text field?

The trigger:

CREATE TRIGGER trg_trackingRecord_forDelete ON case_action

FOR DELETE

AS

DECLARE @trackID AS varchar(24)
DECLARE @trackEvent AS varchar(6)
DECLARE @trackDate AS datetime
DECLARE @trackUser AS varchar(15)
DECLARE @csa_id AS varchar(18)
DECLARE @case_type AS varchar(3)
DECLARE @actn_menu_id AS varchar(1)
DECLARE @actn_code AS varchar(4)
DECLARE @actn_version_no AS smallint
DECLARE @csm_caseno AS varchar(14)
DECLARE @action_description AS varchar(30)
DECLARE @csa_creation_date AS datetime
DECLARE @csa_date1 AS datetime
DECLARE @csa_date2 AS datetime
DECLARE @csa_date3 AS datetime
DECLARE @csa_time1 AS datetime
DECLARE @csa_time2 AS datetime
DECLARE @csa_time3 AS datetime
DECLARE @csa_ampm1 AS varchar(1)
DECLARE @csa_ampm2 AS varchar(1)
DECLARE @csa_ampm3 AS varchar(1)
/*DECLARE @csa_notes AS text*/
DECLARE @csa_hold_flag AS varchar(1)
DECLARE @csa_disp AS varchar(4)
DECLARE @csa_assigned_to AS varchar(4)
DECLARE @csa_done_by AS varchar(4)
DECLARE @csa_calendar_tag AS varchar(4)
DECLARE @csa_report_tag AS varchar(4)
DECLARE @csa_updated_by AS varchar(4)
DECLARE @csa_updated AS datetime
DECLARE @csa_X_coord AS numeric(13, 10)
DECLARE @csa_Y_coord AS numeric(13, 10)
DECLARE @csa_ivr_confirm_no AS numeric(38, 0)
DECLARE @csa_submitted_from_wireless AS datetime
DECLARE @csa_start_mileage AS numeric(9, 2)
DECLARE @csa_end_mileage AS numeric(9, 2)
DECLARE @csa_total_mileage AS numeric(9, 2)
DECLARE @csa_vehicle_id AS varchar(17)
DECLARE @csa_start_time AS datetime
DECLARE @csa_end_time AS datetime
DECLARE @csa_total_time AS numeric(4, 2)


/*Grab current values from Deleted record*/
SELECT @csa_id = csa_id FROM Deleted
SELECT @case_type = case_type FROM Deleted
SELECT @actn_menu_id = actn_menu_id FROM Deleted
SELECT @actn_code = actn_code FROM Deleted
SELECT @actn_version_no = actn_version_no FROM Deleted
SELECT @csm_caseno = csm_caseno FROM Deleted
SELECT @action_description = action_description FROM Deleted
SELECT @csa_creation_date = csa_creation_date FROM Deleted
SELECT @csa_date1 = csa_date1 FROM Deleted
SELECT @csa_date2 = csa_date2 FROM Deleted
SELECT @csa_date3 = csa_date3 FROM Deleted
SELECT @csa_time1 = csa_time1 FROM Deleted
SELECT @csa_time2 = csa_time2 FROM Deleted
SELECT @csa_time3 = csa_time3 FROM Deleted
SELECT @csa_ampm1 = csa_ampm1 FROM Deleted
SELECT @csa_ampm2 = csa_ampm2 FROM Deleted
SELECT @csa_ampm3 = csa_ampm3 FROM Deleted
/*SELECT @csa_notes = csa_notes FROM Deleted*/
SELECT @csa_hold_flag = csa_hold_flag FROM Deleted
SELECT @csa_disp = csa_disp FROM Deleted
SELECT @csa_assigned_to = csa_assigned_to FROM Deleted
SELECT @csa_done_by = csa_done_by FROM Deleted
SELECT @csa_calendar_tag = csa_calendar_tag FROM Deleted
SELECT @csa_report_tag = csa_report_tag FROM Deleted
SELECT @csa_updated_by = csa_updated_by FROM Deleted
SELECT @csa_updated = csa_updated FROM Deleted
SELECT @csa_X_coord = csa_X_coord FROM Deleted
SELECT @csa_Y_coord = csa_Y_coord FROM Deleted
SELECT @csa_ivr_confirm_no = csa_ivr_confirm_no FROM Deleted
SELECT @csa_submitted_from_wireless = csa_submitted_from_wireless FROM Deleted
SELECT @csa_start_mileage = csa_start_mileage FROM Deleted
SELECT @csa_end_mileage = csa_end_mileage FROM Deleted
SELECT @csa_total_mileage = csa_total_mileage FROM Deleted
SELECT @csa_vehicle_id = csa_vehicle_id FROM Deleted
SELECT @csa_start_time = csa_start_time FROM Deleted
SELECT @csa_end_time = csa_end_time FROM Deleted
SELECT @csa_total_time = csa_total_time FROM Deleted


/*Set up tracking record values*/
SELECT @trackID = 'DELETE' + csa_id FROM Deleted
SELECT @trackEvent = 'DELETE'
SELECT @trackDate = GETDATE()
SELECT @trackUser = CURRENT_USER


/*Update tracking table*/
INSERT INTO dbo.track_case_action
(
trackID,
trackEvent,
trackDate,
trackUser,
csa_id,
case_type,
actn_menu_id,
actn_code,
actn_version_no,
csm_caseno,
action_description,
csa_creation_date,
csa_date1,

csa_date2,
csa_date3,
csa_time1,
csa_time2,
csa_time3,
csa_ampm1,
csa_ampm2,
csa_ampm3,
/*csa_notes, */
csa_hold_flag,
csa_disp,
csa_assigned_to,
csa_done_by,
csa_calendar_tag,
csa_report_tag,
csa_updated_by,
csa_updated,
csa_X_coord,
csa_Y_coord,
csa_ivr_confirm_no,
csa_submitted_from_wireless,
csa_start_mileage,
csa_end_mileage,
csa_total_mileage,
csa_vehicle_id,
csa_start_time,
csa_end_time,
csa_total_time
)
VALUES
(
@trackID,
@trackEvent,
@trackDate,
@trackUser,
@csa_id,
@case_type,
@actn_menu_id,
@actn_code,
@actn_version_no,
@csm_caseno,
@action_description,
@csa_creation_date,
@csa_date1,
@csa_date2,
@csa_date3,
@csa_time1,
@csa_time2,
@csa_time3,
@csa_ampm1,
@csa_ampm2,
@csa_ampm3,
/*@csa_notes,*/
@csa_hold_flag,
@csa_disp,
@csa_assigned_to,
@csa_done_by,
@csa_calendar_tag,
@csa_report_tag,
@csa_updated_by,
@csa_updated,
@csa_X_coord,
@csa_Y_coord,
@csa_ivr_confirm_no,
@csa_submitted_from_wireless,
@csa_start_mileage,
@csa_end_mileage,
@csa_total_mileage,
@csa_vehicle_id,
@csa_start_time,
@csa_end_time,
@csa_total_time
)



The source table:

[dbo].[case_action] (
[csa_id] [varchar] (18) NOT NULL ,
[case_type] [varchar] (3) NOT NULL ,
[actn_menu_id] [varchar] (1) NOT NULL ,
[actn_code] [varchar] (4) NOT NULL ,
[actn_version_no] [smallint] NOT NULL ,
[csm_caseno] [varchar] (14) NOT NULL ,
[action_description] [varchar] (30) NULL ,
[csa_creation_date] [datetime] NULL ,
[csa_date1] [datetime] NULL ,
[csa_date2] [datetime] NULL ,
[csa_date3] [datetime] NULL ,
[csa_time1] [datetime] NULL ,
[csa_time2] [datetime] NULL ,
[csa_time3] [datetime] NULL ,
[csa_ampm1] [varchar] (1) NULL ,
[csa_ampm2] [varchar] (1) NULL ,
[csa_ampm3] [varchar] (1) NULL ,
[csa_notes] [text] NULL ,
[csa_hold_flag] [varchar] (1) NULL ,
[csa_disp] [varchar] (4) NULL ,
[csa_assigned_to] [varchar] (4) NULL ,
[csa_done_by] [varchar] (4) NULL ,
[csa_calendar_tag] [varchar] (4) NULL ,
[csa_report_tag] [varchar] (4) NULL ,
[csa_updated_by] [varchar] (4) NULL ,
[csa_updated] [datetime] NULL ,
[csa_X_coord] [numeric](13, 10) NULL ,
[csa_Y_coord] [numeric](13, 10) NULL ,
[csa_ivr_confirm_no] [numeric](38, 0) NULL ,
[csa_submitted_from_wireless] [datetime] NULL ,
[csa_start_mileage] [numeric](9, 2) NULL ,
[csa_end_mileage] [numeric](9, 2) NULL ,
[csa_total_mileage] [numeric](9, 2) NULL ,
[csa_vehicle_id] [varchar] (17) NULL ,
[csa_start_time] [datetime] NULL ,
[csa_end_time] [datetime] NULL ,
[csa_total_time] [numeric](4, 2) NULL
)

The destination table:

CREATE TABLE [dbo].[track_case_action] (
[trackID] [varchar] (24) NOT NULL ,
[trackEvent] [varchar] (6) NOT NULL ,
[trackDate] [datetime] NOT NULL ,
[trackUser] [varchar] (15) NOT NULL ,
[csa_id] [varchar] (18) NOT NULL ,
[case_type] [varchar] (3) NOT NULL ,
[actn_menu_id] [varchar] (1) NOT NULL ,
[actn_code] [varchar] (4) NOT NULL ,
[actn_version_no] [smallint] NOT NULL ,
[csm_caseno] [varchar] (14) NOT NULL ,
[action_description] [varchar] (30) NULL ,
[csa_creation_date] [datetime] NULL ,
[csa_date1] [datetime] NULL ,
[csa_date2] [datetime] NULL ,
[csa_date3] [datetime] NULL ,
[csa_time1] [datetime] NULL ,
[csa_time2] [datetime] NULL ,
[csa_time3] [datetime] NULL ,
[csa_ampm1] [varchar] (1) NULL ,
[csa_ampm2] [varchar] (1) NULL ,
[csa_ampm3] [varchar] (1) NULL ,
[csa_notes] [text] NULL ,
[csa_hold_flag] [varchar] (1) NULL ,
[csa_disp] [varchar] (4) NULL ,
[csa_assigned_to] [varchar] (4) NULL ,
[csa_done_by] [varchar] (4) NULL ,
[csa_calendar_tag] [varchar] (4) NULL ,
[csa_report_tag] [varchar] (4) NULL ,
[csa_updated_by] [varchar] (4) NULL ,
[csa_updated] [datetime] NULL ,
[csa_X_coord] [numeric](13, 10) NULL ,
[csa_Y_coord] [numeric](13, 10) NULL ,
[csa_ivr_confirm_no] [numeric](38, 0) NULL ,
[csa_submitted_from_wireless] [datetime] NULL ,
[csa_start_mileage] [numeric](9, 2) NULL ,
[csa_end_mileage] [numeric](9, 2) NULL ,
[csa_total_mileage] [numeric](9, 2) NULL ,
[csa_vehicle_id] [varchar] (17) NULL ,
[csa_start_time] [datetime] NULL ,
[csa_end_time] [datetime] NULL ,
[csa_total_time] [numeric](4, 2) NULL
)

 

[SQLSister]

Let's start with the fact that your trigger will not work properly if a mulitple record insert is done. Never ever write a trigger this way, always assume that multiple records will be inserted simultaneously as sooner or later they will be.

Use a set based insert do not use variables.

I think you will still have trouble with the text field though. I don;t think inserted and deleted store text data. This is what I have done inthe past - I have inserted everything except the text field in the insert statement. Then I updated based on my id field from inserted joining to the original table to get the text data. You could do this all in one insert statment as well I suppose, I just usually found it easier to do in two steps, so I only had to join to the bigger table is there was data in the text field.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[ramsfield]

The application limits the insertion of records to one at a time. The text field is the important part, we have two more triggers that works with this one, one for deletes and one for updates. We really need to be able to track what is changing within the text field.

 

[SQLSister]

You misunderstand me. Eveni fhte application only lets one record ata time be inserted ther may be some time when someone is asked to insert a group from the backend - trust me this will happen sooner or later and your code MUST account for the possibility. Triggers must never uinder any circumstances be =wruittento expect only one record inserted.

Like I said in my second paragraphy simply link the inserted to the actual table and pull the column that way.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief