Creating a complicated trigger

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?


codemonkey2480Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ralmadaConnect With a Mentor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
hi....

sorry but can u pls re-explain..........
0
 
codemonkey2480Author Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Bhavesh ShahLead AnalysistCommented:
Hi,

STEP 2 is bit unclear.SORRY

you wanted to write '1001  Motor-Gear-Brake' value in separate colmun??
0
 
ralmadaConnect With a Mentor Commented:
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
 
codemonkey2480Author Commented:
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
 
ralmadaCommented:
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
 
codemonkey2480Author Commented:
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
 
codemonkey2480Author Commented:
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
All Courses

From novice to tech pro — start learning today.