Problem with Trigger - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Problem with Trigger

Author

Topic

stevo_3
Starting Member

20 Posts

Posted - 2006-01-10 : 10:28:56

Hello,

i got a trigger i get the folowwing error:
Error 2739: The text, ntext, and image data types are invalid for local variables

But the problem is these fields needs to be inserted absolutely in my database:

Here is my trigger (very large one

CREATE TRIGGER [portima1] ON dbo.workitem
FOR INSERT, UPDATE

AS

declare @id int
declare @timestamp timestamp
declare @link_parent_number int
declare @link_type_lookup_id int
declare @number int
declare @is_last nchar
declare @has_attachments nchar
declare @version int
declare @source nvarchar
declare @external_reference nvarchar
declare @priority_lookup_id int
declare @status_lookup_id int
declare @category_lookup_id int
declare @type_lookup_id int
declare @category_tree_value nvarchar
declare @assigned_to_worker_id int
declare @created_by_worker_id int
declare @modified_by_worker_id int
declare @created_by_worker_contact_id int
declare @modified_by_worker_contact_id int
declare @created_on datetime
declare @start_on datetime
declare @due_on datetime
declare @modified_on datetime
declare @minutes_spent int
declare @action nvarchar
declare @title nvarchar
declare @managed_object_id int
declare @contact_id int
declare @tag_collection_id int
declare @is_scheduled nchar
declare @bulletin_status nchar
declare @comment ntext
declare @auxdata ntext
declare @total_minutes_spent int
declare @owned_by_worker_id int
declare @_ResourceGuid uniqueidentifier
declare @rating int
declare @comment_visible_to_guest nchar
declare @rules_fired nvarchar
declare @updatelock nchar
declare @urgency_lookup_id int
declare @impact_lookup_id int
declare @close_code_lookup_id int
declare @portimaid nvarchar
declare @officeid nvarchar
declare @ticketstatus nvarchar
declare @new_comment ntext

IF EXISTS (SELECT * FROM workitem where portimaid=@portimaid)

begin
UPDATE workitem
SET comment = comment + @new_comment
end
else

UPDATE workitem
SET portimaid = SUBSTRING(title, 36, 6)

UPDATE workitem
SET officeid= SUBSTRING (title, 58, 5)

UPDATE workitem
SET workitem.contact_id= contact.id FROM workitem inner join contact
ON workitem.officeid=contact.title

INSERT INTO
workitem(id,link_parent_number,link_type_lookup_id,number,is_last,has_attachments,version,source,external_reference,priority_lookup_id,status_lookup_id,category_lookup_id,type_lookup_id,category_tree_value,assigned_to_worker_id,created_by_worker_id,modified_by_worker_id,created_by_worker_contact_id,modified_by_worker_contact_id,created_on,start_on,due_on,modified_on,minutes_spent,action,title,managed_object_id,contact_id,tag_collection_id,is_scheduled,bulletin_status,comment,auxdata,total_minutes_spent,owned_by_worker_id,_ResourceGuid,rating,comment_visible_to_guest,rules_fired,updatelock,urgency_lookup_id,impact_lookup_id,close_code_lookup_id)
VALUES
(@id,@link_parent_number,@link_type_lookup_id,@number,@is_last,@has_attachments,@version,@source,@external_reference,@priority_lookup_id,@status_lookup_id,@category_lookup_id,@type_lookup_id,@category_tree_value,@assigned_to_worker_id,@created_by_worker_id,@modified_by_worker_id,@created_by_worker_contact_id,@modified_by_worker_contact_id,@created_on,@start_on,@due_on,@modified_on,@minutes_spent,@action,@title,@managed_object_id,@contact_id,@tag_collection_id,@is_scheduled,@bulletin_status,@comment,@auxdata,@total_minutes_spent,@owned_by_worker_id,@_ResourceGuid,@rating,@comment_visible_to_guest,@rules_fired,@updatelock,@urgency_lookup_id,@impact_lookup_id,@close_code_lookup_id)

sorry about the mess

Anyone got an idea how i can solve this?

Thx

Steve

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-10 : 11:13:27

You cannot declare a local variable of datatype text, ntext, and image.

This trigger makes no sense, since you have no logic to populate any of the variables that you declared.

What are you trying to accomplish?

CODO ERGO SUM

stevo_3
Starting Member

20 Posts

Posted - 2006-01-10 : 13:09:45

Hello,

i'm going to explain what i want to achieve:

i got a table workitem with folowwing fields:
id(key),title, comment, ..., portimaid, officeid
1 blabla, hello ,...., 559542 , 1234

before inserting the record he needs to detect that he founds already the portimaid in the record list (unique field),
so he needs to update the comment only of an existing record

for example i want to add folowwing record:
id(key),title ,comment , ..., portimaid, officeid
55 , blabla2222, hello222 ,...., 559542 , 1234

as you can see i just want that he adds to comment to this existing record sow i want to have this as result:

1 blablablabla2222, hellohello222 ,....,

559542 , 1234

-------------------------------------------------------------------
if he doesn't detect the same portimaid do the folowwing:

insert a whole new record: and these are all the fields that are
coming from an application called altiris, but i don't know a
command to add a whole record

Thanks

Steve

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-10 : 14:07:05

U'd be able to use a Stored Procedure, instead of a trigger ???

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60053
jippieeeeeee

stevo_3
Starting Member

20 Posts

Posted - 2006-01-10 : 14:27:09

Yes,

maybe stored procedure but how i can add my record then,
do i need to consult the "inserted" table ?

Steve

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-10 : 14:40:36

[code]if exists (select ... from workitem where ....)
Begin
Update workitem
Set
comment = comment + @new_comment,
portimaid = SUBSTRING(title, 36, 6)
officeid= SUBSTRING (title, 58, 5)
FROM workitem inner join contact
ON workitem.officeid=contact.title
End
Else
Begin
INSERT INTO workitem (....) values (....)
End[/code]

stevo_3
Starting Member

20 Posts

Posted - 2006-01-10 : 15:07:19

Sorry to bother you but i still don't have the clue:

in the trigger:

CREATE TRIGGER dbo.portima1
FOR INSERT, DELETE
AS

USE workitem
EXEC sp_portima1(?)
GO

in the procedure:

CREATE PROCEDURE portima1
declare @portimaid nvarchar
begin

if exists (select portimaid from workitem where SUBSTRING(title, 36, 6)=portimaid)
Begin
Update workitem
Set
comment = comment + @new_comment,
portimaid = SUBSTRING(title, 36, 6)
officeid= SUBSTRING (title, 58, 5)
FROM workitem inner join contact
ON workitem.officeid=contact.title
End
Else
Begin
INSERT INTO workitem (id,link_parent_number,link_type_lookup_id,number,is_last,has_attachments,version,source,external_reference,priority_lookup_id,status_lookup_id,category_lookup_id,type_lookup_id,category_tree_value,assigned_to_worker_id,created_by_worker_id,modified_by_worker_id,created_by_worker_contact_id,modified_by_worker_contact_id,created_on,start_on,due_on,modified_on,minutes_spent,action,title,managed_object_id,contact_id,tag_collection_id,is_scheduled,bulletin_status,comment,auxdata,total_minutes_spent,owned_by_worker_id,_ResourceGuid,rating,comment_visible_to_guest,rules_fired,updatelock,urgency_lookup_id,impact_lookup_id,close_code_lookup_id)
values (....)
**** This is just my problem: i don't have values for this,
they are automaticly updated by an application (altiris helpdesk solution) designed in ASP (.NET)
there is just a command who just sais add record

and i need to analyze the records before i may add them

End

end

This is going to deep for me

Steve

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-10 : 15:23:53

-- Nothing to do with triggers --

Write a stored procedure, which takes the data u supplied as input parameters
In the procedure do the coding as I have given
[I gave the logic only u do the filling of the remaining]

The code is checking whether a particular record exists, and
if so update it (what needs to be updated will be determined by u)
if no record, insert the data

Invoke ur stored procedure with appropriate parameters from ur client application.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources