August 9, 2011 at 2:01 pm
I am creating a Trigger to tracking all DML activities on a table, and same the old data into history.
Because there is TEXT fields in the main table, I have to use INSTEAD OF Trigger in order to be able to retrieve the TEXT value from the "inserted" and "deleted" table. However, this introduced a different problem. DELETE and UPDATE works fine, but on INSERT, the "inserted" table does not contain the value of the IDENTITY column. I tried to use @@IDENTITY and SCOPE_IDENTITY(), but they only works on single row INSERT, does not work for bulk insert.
Does any on have any idea on resolving this issue?
Here is the code:
CREATE TABLE [dbo].[Activities](
[ActivityId] [int] IDENTITY(1,1) NOT NULL,
[Description] [text] NULL,
[UpdateDate] [datetime] NOT NULL,
[UpdatedBy] [int] NOT NULL,
)
CREATE TABLE [dbo].[ActivitiesHist](
[ActivityId] [int] NOT NULL,
[Description] [text] NULL,
[UpdateDate] [datetime] NOT NULL,
[UpdatedBy] [int] NOT NULL,
[Action] varchar(10) NOT NULL
)
GO
CREATE TRIGGER [dbo].[TRG_Activities_IUD] ON [dbo].[Activities]
INSTEAD OF INSERT,DELETE,UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0
RETURN -- NO NEED TO TAKE FURTHER ACTIONS
SET NOCOUNT ON
DECLARE @ActionType VARCHAR(10)
IF NOT EXISTS (SELECT * FROM deleted)
BEGIN
SET@ActionType = 'INSERT' -- system trigger table "deleted" should be empty on INSERT action
INSERT INTO Activities
(
Description,
UpdateDate,
UpdatedBy
)
SELECT
Description,
UpdateDate,
UpdatedBy
FROM inserted
IF @@ERROR <>0
BEGIN
RAISERROR('FAIL TO INSERT Into Activities',16,1)
END
END
ELSE IF NOT EXISTS (SELECT * FROM inserted)
BEGIN
SET@ActionType = 'DELETE' -- on DELETE, system trigger table "deleted" has data but "inserted" table should be empty
DELETE Activities
FROM Activities a, deleted d
WHERE a.ActivityId=d.ActivityId
IF @@ERROR <>0
BEGIN
RAISERROR('FAIL TO DELETE ENTRY In Activities',16,1)
END
END
ELSE -- on UPDATE, both system trigger table "inserted" and "deleted" must have data
BEGIN
SET@ActionType = 'UPDATE'
UPDATE Activities
SET
Description=i.Description,
UpdateDate=i.UpdateDate,
UpdatedBy=i.UpdatedBy
FROM Activities a, inserted i
WHERE a.ActivityId=i.ActivityId
IF @@ERROR <>0
BEGIN
RAISERROR('FAIL TO UPDATE Activities',16,1)
END
END
-- Create History Entry
INSERT INTO ActivitiesHist
(
ActivityId,
Description,
UpdateDate,
UpdatedBy,
Action
)
SELECT
ActivityId,
Description,
UpdateDate,
UpdatedBy,
@ActionType
FROM inserted WHERE @ActionType='INSERT' -- Take data from "inserted" table on INSERT Action
UNION ALL-- ONLY ONE QUERY WILL RETURN RESULTS FROM THIS UNION
SELECT
ActivityId,
Description,
UpdateDate,
UpdatedBy,
@ActionType
FROM deleted WHERE @ActionType <>'INSERT' -- Take data from "deleted" table on UPDATE and DELTE Actions
IF @@ERROR <>0
BEGIN
RAISERROR('FAIL TO CRREATE HISTORY ENTRY In ActivitiesHist',16,1)
END
END
August 10, 2011 at 8:38 am
Can you change your description field to nvarchar(max) instead of text? text is a deprecated datatype and nvarchar(max) can hold an enormous amount of data. As for the value of your identity, it can't possibly be populated. You are creating an instead of insert, this means the value has not yet been inserted so the identity can't possibly have a value yet. If you can change your datatype all your problems will go away and you can use a standard trigger.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2011 at 10:50 am
Thanks for your reply. I had to convince business to agree with limiting the length to 8000, or I have to have the application to explicitly record history on each insert/delete/update.
Still hope there is better way:)
August 10, 2011 at 12:23 pm
Of course varchar(max) is not 8,000 characters. It is up to 2GB. Here is the reference in BOL.
varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying.
If you can't fit a description in that then I would say it isn't a description and it is more like an entire bookstore.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2011 at 7:49 pm
Right.
Thanks
August 18, 2011 at 2:46 pm
Change
BEGIN
SET @ActionType = 'UPDATE'
UPDATE Activities
SET
Description = i.Description,
UpdateDate = i.UpdateDate,
UpdatedBy = i.UpdatedBy
FROM Activities a, inserted i
WHERE a.ActivityId=i.ActivityId
IF @@ERROR <>0
BEGIN
RAISERROR('FAIL TO UPDATE Activities',16,1)
END
to
BEGIN
SET @ActionType = 'UPDATE'
UPDATE a
SET
a.Description = i.Description,
a.UpdateDate = i.UpdateDate,
a.UpdatedBy = i.UpdatedBy
FROM Activities as a
inner join inserted as i ON i.ActivityId = a.ActivityId
IF @@ERROR <>0
BEGIN
RAISERROR('FAIL TO UPDATE Activities',16,1)
END
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply