Solved

Creating a complicated trigger

Posted on 2010-11-18
9
432 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

739 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