Solved

Creating a complicated trigger

Posted on 2010-11-18
9
424 Views
Last Modified: 2012-06-27
I need to create a  trigger on a table T1 on INSERT. The table has keys req_key and eq_key.

The trigger needs to SELECT equipment for a given req_key and eq_key and string concatenated them as one long string.

Eg: req_key=1001 and eq_key=10,11,12, The results for this select will be
1001 10 Motor
1001 11 Gear
1001 12 Brake

The trigger logic should convert this as 1001 10  Motor-Gear-Brake.

How do I do this?


0
Comment
Question by:codemonkey2480
  • 4
  • 3
  • 2
9 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34163407
hi....

sorry but can u pls re-explain..........
0
 

Author Comment

by:codemonkey2480
ID: 34163518
In an INSERT trigger on table T1,

STEP 1: I need to get the list of equipment for req_key and eq_key. The results of this SELECT will look like this

req_key           eq_key           equipment
1001                    10                   Motor
1001                    11                   Gear
1001                    12                    Brake

STEP 2: I need to write logic this to rewrite this as 1001  Motor-Gear-Brake.
Meaning for req-key 1001 my equipment list is Motor-Gear-Brake.

STEP 3: INSERT this into another table T2

All of the above steps need to be done with in the INSERT TRIGGER logic.

Does this make sense? please let me know.

thanks for your input.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34163557
Hi,

STEP 2 is bit unclear.SORRY

you wanted to write '1001  Motor-Gear-Brake' value in separate colmun??
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 500 total points
ID: 34164722
Step 2 can be easily done using query #1 below

Step3 If you want to insert the result of query#1 into Table2 then check query#2 below

Now what puzzles me is step 1, why you need this in a trigger?
--query #1
select distinct 
	req_key,
	stuff((select '- ' + equipment from Table1 where req_key = T1.req_key for xml path('')), 1, 2, '') as eqlist
from Table1 T1

--query #2
insert Table2 --please note that I'm assuming table two has two columns only. Please adjust accordingly.
select distinct 
	req_key,
	stuff((select '- ' + equipment from Table1 where req_key = T1.req_key for xml path('')), 1, 2, '') as eqlist
from Table1 T1

Open in new window

0
 

Author Comment

by:codemonkey2480
ID: 34168888
Brichsoft, yes I want to write both of them in separate columns.

ralmada, I think thats right we can combine Step 1 and 2 in the same select query.

So How would the entire trigger look?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 34173526
ok, first try the above query by it self to see if that's what you're after.

Secondly, do you really need this in a trigger? I'm asking because I cannot make sense of it. A trigger doesn't look like the place where you want this type of operations.

0
 

Author Comment

by:codemonkey2480
ID: 34175176
ralmada
I am a newbie so you have to enlighten me. Why should this not be in a trigger?
Should this be in the application code?
Let me know your thoughts.

thanks for your input.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 34177043
Ok, so

1) From what I see you are trying to present the data in a specific format, This is fine doing at the presentation layer, but not to store in a database, because it's not following normalization best practices.

2) check this link, which although it's from IBM explains, I find it explains very well when to use a trigger:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlt.doc/sqltmst316.htm
It says,
>>You can use triggers to perform the following actions, as well as others that are not found in this list:
Create an audit trail of activity in the database. For example, you can track updates to the orders table by updating corroborating information to an audit table.

Implement a business rule. For example, you can determine when an order exceeds a customer's credit limit and display a message to that effect.

Derive additional data that is not available within a table or within the database. For example, when an update occurs to the quantity column of the items table, you can calculate the corresponding adjustment to the total_price column.

Enforce referential integrity. When you delete a customer, for example, you can use a trigger to delete corresponding rows (that is, rows that have the same customer number) in the orders table.

3) As a result, of number 2 you can see that implementing this logic in a trigger doesn't make much sense.
so to answer you question,
>>Should this be in the application code?<<
Yes, i think so. considering that all you want is to display the data in a specific format. If that's not the case, please explain a bit better what it's your final goal with presenting the data in that way.

Hope this helps
0
 

Author Comment

by:codemonkey2480
ID: 34180141
ralmada,
Thank you for the detailed explanation. I am trying to present the data in a specific format for another application. My application is providing input to another 3rd party application that wants the input in a specific format.

Every single table in my application follows RDBMS rules and is normalized except for these that provide data input to this third party application.  

I think the best way is to handle it in my data access code (instead of a trigger) and save the information to the table.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question