Using variables in INSERT Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using variables in INSERT Trigger

Author

Topic

chosey
Starting Member

6 Posts

Posted - 2012-10-21 : 10:05:34

Hello experts,

I'm trying to create an AFTER INSERT trigger in one of my databases which will create some log information for INSERTS.
To achieve this, I've created a few variables in my code which I want to hold some of the columns information.
The issue is that some of the table columns are of ntext type and when I try to compile my code, I get the following error:

Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

I get this error for the following line:

select @question = (select question from inserted)

Can anyone please assist?

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-21 : 10:16:26

Cast them to NVARCHAR(MAX) or VARBINARY(MAX).

However, the statement that you posted indicates another problem that you are likely to run into - and that is the fact that the INSERTED and DELETED tables can have more than one row. Your statement will work correctly only in cases where there is one row in the INSERTED table.

If you can post the code people on the forum would be able to offer suggestions on how to rewrite it to avoid this problem.

chosey
Starting Member

6 Posts

Posted - 2012-10-21 : 10:23:16

Thanks for the quick reply.
I've already tried to cast it in the select clause but I ran into the same problem.
Example:
select @question = (select cast(question as nvarchar(max)) from inserted)

Here is the full code (sorry, it's a bit messy):

-----------TRIGGER------------------------------------------------------------
-----------GET Data from SysAID------------
CREATE TRIGGER KB_Trigger ON SysAidIT.dbo.faq
AFTER INSERT
AS
BEGIN
------- Decalre Variables for SysAID
declare @title nvarchar(4000)
declare @answer nvarchar(1000)
declare @question nvarchar(1000)
declare @body nvarchar (4000)
------- Declare Variables for KBMGRPro
declare @increment int
set @increment = (select cast(cast (var_value as nvarchar(10)) as int) from KBMGRPro.dbo.sys_settings where var_name='code_increment')
set @increment = @increment+1

set @title = (select title from inserted)
select @question = (select cast(question as nvarchar(max)) from inserted)
select @answer = (select i.answer from SysAidIT.dbo.faq f, inserted i, deleted d where f.id = i.id )
set @body = 'QUESTION: ' + @question + '<hr>ANSWER: ' + @answer

----- INSERT QUERY
INSERT INTO KBMGRPro.dbo.faq_articles (account_id, code, email, question, answer, faq_date, real_creation_date, status, status_change_date, views, rating, "public", public_start_date,
public_end_date, author, editor, freeAccess, meta_keywords, meta_description, plain_text, locked, lock_date, lock_user_id, draft, featured, KEYWORD_JSGUI, KEYWORD_KMP, KEYWORD_JSGUI_URL, KEYWORD_KMP_URL)
VALUES ('2','AA-'+convert(nvarchar(5),@increment), 'admin@email.com', @title, @body, GETDATE(),
GETDATE(), 'moderated', GETDATE(), '0', '0', 'Y', NULL, NULL, '2', '2', 'Y', NULL, NULL, NULL, 'N',
NULL, '0', 'N', 'N', NULL, NULL, NULL, NULL)
INSERT INTO KBMGRPro.dbo.search_index (faq_id,attachment_id,account_id,question,meta_keywords,meta_description,plain_text,back_end_custom_fields,front_end_custom_fields,comments) VALUES (@increment,0,2,'test2',' ',' ','test2_answer',' ',' ',' ')
INSERT INTO KBMGRPro.dbo.articles_categories (faq_id,category_id) VALUES (@increment,7);
UPDATE KBMGRPro.dbo.sys_settings set var_value=convert(ntext,convert(nvarchar(10),@increment)) where var_name='code_increment'

END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-21 : 10:36:50

Instead of using intermediate variables, select from or join to INSERTED or deleted tables as appropriate. As an example, part of the code you posted can be changed like shown in read below:

	INSERT INTO KBMGRPro.dbo.faq_articles
	  (
	    account_id,
	    code,
	    email,
	    question,
	    answer,
	    faq_date,
	    real_creation_date,
	    STATUS,
	    status_change_date,
	    VIEWS,
	    rating,
	    "public",
	    public_start_date,
	    public_end_date,
	    author,
	    editor,
	    freeAccess,
	    meta_keywords,
	    meta_description,
	    plain_text,
	    locked,
	    lock_date,
	    lock_user_id,
	    draft,
	    featured,
	    KEYWORD_JSGUI,
	    KEYWORD_KMP,
	    KEYWORD_JSGUI_URL,
	    KEYWORD_KMP_URL
	  )
	SELECT 
	    '2',
	    'AA-' + CONVERT(NVARCHAR(5), @increment),
	    'admin@email.com',
	    INSERTED.title,
	    CAST(INSERTED.question AS NVARCHAR(MAX)),
	    GETDATE(),
	    GETDATE(),
	    'moderated',
	    GETDATE(),
	    '0',
	    '0',
	    'Y',
	    NULL,
	    NULL,
	    '2',
	    '2',
	    'Y',
	    NULL,
	    NULL,
	    NULL,
	    'N',
	    NULL,
	    '0',
	    'N',
	    'N',
	    NULL,
	    NULL,
	    NULL,
	    NULL
	FROM INSERTED

Regarding the NEXT/IMAGE data type, I have not run into this,so I am unable to offer suggestions other than trying to cast them to NVARCHAR or VARBINARY. See if this link will help you. They join to the original source table and then pick up the columns from that table instead of the INSETED table:
http://lazycodeprogrammer.blogspot.com/2009/08/how-to-use-inserteddeleted-with.html

chosey
Starting Member

6 Posts

Posted - 2012-10-21 : 11:09:12

Thanks again,
I've converted my code as you suggested and it is much cleaner now.
I'm afraid the issue persists.
I've tried before the link you provided, but it doesn't appear like the same issue.

Any other suggestions? :/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-21 : 12:54:22

I don't know of any other work around. If you have the ability to do so, changing the columns in the original tables to NVARCHAR(MAX) would certainly fix the problem. However, you would need to test all the other procedures, views etc. that use the table to make sure that they will not be adversely affected.

The workaround as I understood is as follows:

-- Create two tables
CREATE TABLE A(id INT, col1 NTEXT);
CREATE TABLE B(id INT, col1 NTEXT);
GO

-- Try to create trigger. This does not work because
-- of NTEXT column.
CREATE TRIGGER ATrigger ON A FOR INSERT, UPDATE AS
INSERT INTO B 
SELECT 
	id, CAST(col1 AS NVARCHAR(MAX)) FROM INSERTED;
GO

-- Workaround.
CREATE TRIGGER ATrigger ON A FOR INSERT, UPDATE AS
INSERT INTO B
SELECT
	a.id,
	a.col1
FROM
	INSERTED i
	INNER JOIN A ON a.id = i.id;
GO

-- test
INSERT INTO A VALUES (1,'ab');
SELECT * FROM A;
SELECT * FROM B;
GO

DROP TABLE a;
DROP TABLE b;

chosey
Starting Member

6 Posts

Posted - 2012-10-22 : 04:10:29

Yep, that's the solution I've tested myself.
The issue is that it enters NULLs in table A. (I guess it's the original transaction hasn't ended yet).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-22 : 07:27:51

quote:
Originally posted by chosey

Yep, that's the solution I've tested myself.
The issue is that it enters NULLs in table A. (I guess it's the original transaction hasn't ended yet).

Hm.. I don't see that. In the example I posted, if I insert something into Table A, the code works as expected - i.e., in the trigger, it takes the values I inserted and puts them into Table B.

chosey
Starting Member

6 Posts

Posted - 2012-10-22 : 07:31:13

Maybe I'm doing it wrong.
Can you give me an example how would you implement this in my code?

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources