Using Many Triggers on one table 1

[thegiraffe]

I have a table that i have all pending tasks held in, there are many different stages with the innitial setup of a client record so there are many triggers on this pending table.

It is already becoming quite a lengthy trigger,
lots of
if CODE = 100
insert this pending for run
if CODE = 101
insert this pending for run
if CODE = 102
insert this pending for run

Currently there are about 10 maybe more if statements and about 20 inserts in one trigger.

I need to do a lot more, should I write these all into one trigger as i don't have to set the basic details or would it be advisable to split all of the tasks i need to complete into a few triggers.

I don't want to drain the power with one trigger, any suggestions. Dave

Theres nothing worse than an idea when its the only one we have!

 

[JamesLean]

I would be tempted to have a rethink of your db design Dave.

Scenario: when a new client is added, a list of tasks have to be completed in order (I hope I have understood this right).

Create a Client table: ClientID, Name, etc..

Create a Task table: TaskID, Description

Create a ClientTask table: ClientID, TaskID

When you add a new client, you insert several rows into the ClientTask table - one for each task that needs to be completed (could use trigger here).

Now you can get the pending task for a client by using:

SELECT Description
FROM Task
WHERE TaskID IN (SELECT MIN(TaskID) FROM ClientTask WHERE ClientID = x)

You just need to make sure that your TaskID's are in the correct sequence.

Once a task has been completed for a client you just delete the row from the ClientTask table.

I hope this makes sense!

 

[thegiraffe]

Unfortunately we have purchased a system, and my job is currently to write that system to do what we need, I have thought about the structure and extra tables, but i cannot change anything major in theirs and I can't change their GUI to view my tables.

Also although there are generally the same tasks for each new client, these can only appear as and when other tasks are complete meaning i'm a little stuck in how i approach this.

I will probably try having a seperate table for the descriptions etc (save me typing it all out it the triggers window.

This is a problem thrown at me by a boss who was happier to buy off the shelf software and throw it at the IT dept and expect it to work rather than writing a bespoke system in ASP which would have taken us a mere couple of months extra!!!! (this is not a raw nerve honest) Dave

Theres nothing worse than an idea when its the only one we have!

 

[Warlocks]

Dave,

If you have to do it this way, can you not create another table which holds the code number and another field to hold the SQL to run.

This way, the trigger looks up the code number in the table, gets the sql to run and then use the exec command to execute it. This way, the trigger is dynamic and the only updates is to the table. The trigger code is also kept to a minimum number of lines.

Hope this helps,

Woody.

 

[thegiraffe]

Ok, i see where you're coming from here.

I'm thinking two tables,
ie.
table 1
code, description, notes (these being the info i need)
table 2
code, next_code

this way i can have
table 1
100, enter data
101, check data
102, check email
103, etc etc etc

table 2
100, 101
100, 102

so when 100 is confirmed (deleted) it will set up pending for all in table2 that code matches 100.

Right, just had to set it straight in my own head.

Thankyou Dave

Theres nothing worse than an idea when its the only one we have!

 

[thegiraffe]

Can do this in ASP but i'm struggling to get my head round it here

i want to select from a table if code = 101 (easy)
just two things, how do i refer to fields within a selected record. (it may seem simple but its different to me)

if theres more than one record that match how can i cycle through the records running a task until all is complete.

I don't know what functions and statements work within SQL.

Cheers Dave

Theres nothing worse than an idea when its the only one we have!

 

[Warlocks]

Dave,

This is where it may get complicated but I shall try:

You can use what are called Cursors to actually interrogate the information you return. I have put an example below:

declare @item1 varchar(10)
declare @item2 varchar(10)
declare var_csr cursor for
select name, id from sysobjects
where name like 'sys%'

open var_csr

fetch next from var_csr into @item1, @item2

while (@@fetch_status <> -1)
begin
print 'Item 1: ' + @item1
print 'Item 2: ' + @item2
fetch next from var_csr into @item1, @item2
end

close var_csr
deallocate var_csr

This will display and print the name and id of all the tables in the current database where the name starts with SYS. (Useless I know but it's an example!)

First declare any variables (in this case 2), then declare the cursor you want along with the SQL. You can then use this information like a recordset and move through looking at the information using the while loop to act as the check for .eof.

Hope this helps...

Woody.