Question about trigger with IF clause

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

Transact-SQL (2000)

Question about trigger with IF clause

Author

Topic

stevo_3
Starting Member

20 Posts

Posted - 2006-01-05 : 03:41:32

Hello folks,

here is my trigger:

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

UPDATE workitem
SET ticketstatus= SUBSTRING(title, 14,6)

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

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

UPDATE workitem
SET contact_id=contact.id FROM contact
WHERE EXISTS
(SELECT officeid FROM workitem WHERE contact.external_id = officeid)

And its about a title he needs to check, title is the field in the workitem table... He needs to look if he doesn't find 'Update' or 'Lost' (used substring see upstairs :) this is saved to an existing workitem row in the field "ticketstatus"
i have had some example code to do this but i don't succeed to put it together:
Example code that i had:

declare @row_count int
update workitem
set comment = comment + @new_comment
where portimaid = @portimaid
set @row_count = @@rowcount

if @row_count = 0
begin
insert into YourTable
...
end

OR---------

if exists (select * from YourTable where portimaid = @portimaid)
begin
update YourTable
set comment = comment + @new_comment
end else
begin
insert into YourTable
...
end

Does anyone has an idea

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-05 : 03:56:39

sorry.. it is not very clear what are your trying to achieve and what problem you are facing. Can you post as suggested here ? http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

-----------------
[KH]
2006 a new beginning

stevo_3
Starting Member

20 Posts

Posted - 2006-01-05 : 06:46:38

Sorry i wasn't very clear indeed,

i'm going to post the structure of my database,

i got 2 tables; one trigger on the worktitem table
1. Workitem: id(key), title, portimaid, officeid, contactid (wich
is the same as the id from contact), comment,...
2. Contact: id(key), external_id (wich is updated in the trigger and is the same as the officeid

Now what i want to do:
before a record is added into the table each workitem:
he needs to check the field title,
for example a title can be: Portima:Open; officeid=324234-01;... OR
Updated; officeid=324234-01;...,

now if the title contains 'Updated' as string he needs to update an existing record in the database workitem, he needs to append the comment in an existing record of workitem

if the title contains 'Open' he needs to add a new record in the database

hope it is a bit clear now,
if not please reply,

Thx

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources