SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using variables in INSERT Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chosey
Starting Member

6 Posts

Posted - 10/21/2012 :  10:05:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/21/2012 :  10:16:26  Show Profile  Reply with Quote
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.
Go to Top of Page

chosey
Starting Member

6 Posts

Posted - 10/21/2012 :  10:23:16  Show Profile  Reply with Quote
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 = '<b>QUESTION: <b>' + @question + '<p><hr><p><b>ANSWER: </b>' + @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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/21/2012 :  10:36:50  Show Profile  Reply with Quote
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
Go to Top of Page

chosey
Starting Member

6 Posts

Posted - 10/21/2012 :  11:09:12  Show Profile  Reply with Quote
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? :/
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/21/2012 :  12:54:22  Show Profile  Reply with Quote
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;
Go to Top of Page

chosey
Starting Member

6 Posts

Posted - 10/22/2012 :  04:10:29  Show Profile  Reply with Quote
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).
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/22/2012 :  07:27:51  Show Profile  Reply with Quote
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.
Go to Top of Page

chosey
Starting Member

6 Posts

Posted - 10/22/2012 :  07:31:13  Show Profile  Reply with Quote
Maybe I'm doing it wrong.
Can you give me an example how would you implement this in my code?

Thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000