July 14, 2006 at 12:09 pm
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_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
)
July 14, 2006 at 1:47 pm
Did you try to:
Insert dbo.track_case_action...
Select * from Deleted
Like:
INSERT INTO MyTable (PriKey, Description) SELECT ForeignKey, Description FROM SomeView
See BOL article "Adding Rows by Using INSERT and SELECT INTO"
Regards,Yelena Varsha
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply