Solved

Creating a complicated trigger

Posted on 2010-11-18
9
394 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now