| Author |
Topic |
|
dpnadam
Starting Member
29 Posts |
 Posted - 2011-03-02 : 11:44:37
|
| HiI have a master database containing three tables regarding customer information - CustomerInfo, CustomerContact, CustomerPreferences. Each only has a small number of columns, around 20 in total for all three.I have another database with one table that will hold data from all three of the above tables collectively. I thought of having a trigger on each of the three tables which called a single stored procedure. The stored procedure would just collect all the info for a particular customer and pass it to a stored procedure on the other database to be inserted/updated. I realise I should probably just have little separate stored procedures for each trigger, but there is a reason for doing it in the above way.Obviously one of the problems with this is that if something is changed in all three tables at once, they will call the stored procedure three times. I only want to call it once, is it possible to achieve this?Thanks |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
dpnadam
Starting Member
29 Posts |
Posted - 2011-03-03 : 04:03:32
|
quote:
Originally posted by X002548post the trigger...
Hi BrettThe triggers are simple at the moment really. Each of them will just execute a stored procedure in the same way. Example of one trigger is as follows:CREATE TRIGGER [dbo].[tr_INSERTED_UPDATED_CustomerInfo] ON [dbo].[CustomerInfo] AFTER INSERT, UPDATEAS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @customerID INT; SELECT @customerID = [customerID] FROM [inserted]; EXEC [dbo].[pr_COLLECT_SEND_AllCustomerData] @customerID;END The stored procedure will be something like this:CREATE PROCEDURE [dbo].[pr_COLLECT_SEND_AllCustomerData]@CustomerID INT = NULL ASBEGIN SET NOCOUNT ON; DECLARE @FirstName VARCHAR(30), @LastName VARCHAR(30), @Title VARCHAR(10), @DOB DATETIME, @Address VARCHAR(255), @Postcode VARCHAR(10), @Telephone VARCHAR(30), @ReceiveEmail BIT; -- SELECT STATEMENT WILL BE HERE -- SEND DATA TO OTHER DATABASE STORED PROCEDURE EXEC [OtherDatabase].[dbo].[pr_INSERT_AllCustomerData] @FirstName, @LastName, @Title, @DOB, @Address, @Postcode, @Telephone, @ReceiveEmail, @CustomerID;END Any help or advice would be much appreciated.Thanks. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
dpnadam
Starting Member
29 Posts |
Posted - 2011-03-03 : 11:58:05
|
| Thanks Brett.If only one customer has some details changed at a time though, won't there only be one row to be processed?No not DB2 land, C# etc land. So I know I don't have loads of experience with writing intricate business processes designs using triggers and procedures within SQL server.Why is calling a SP from a trigger a bad idea? I thought I read somewhere, it was better to use SP's to do the 'processing' side of things, especially when it comes to inserting/updating data into other tables.Thanks |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
dpnadam
Starting Member
29 Posts |
Posted - 2011-03-04 : 03:58:35
|
quote:
Originally posted by X002548yes if 1 row was modified...but that is not something you can control.If you are doing this through a procedural language, why not just do it thenBecause a Set based operation will f you all upDo you know what a set based operation is?
I guess strictly speaking I have no control over how many rows are modified, but the front end application only allows one row to be updated. i.e. when you go into a customer details screen and edit some values, to commit the new details you have to click a button.The front end application is not something I have developed so I'm unable to do anything that side. If I was able to I would definitely have preferred to do it that side.Oh right, I'll admit I didn't initially understand what you meant by a set based operation, but upon some quick research the opinion I get is that it is better in most cases to use set based operations over a procedural approach - (links below)[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112384[/url][url]http://www.codeproject.com/KB/database/SetAndProceduralSQL.aspx[/url]What approach would you advise I take on my problem? A front end application, which I have no control over, can update/insert a single customer which will affect multiple tables in a database. Upon any updates/inserts, I need to update/insert a 'master' record into one table in another database. Also depending on a value set in the front end application the 'master' table could be in two or three different databases. So depending on this value, I need to send the updated/inserted row to the appropriate database.Thanks for all your advice thus far. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-03-04 : 17:50:48
|
| How are the updates/inserts managed from the client? Are they updating/inserting to the tables directly, or through stored procedures that you have written? If the former, then you don't have any other options available other than using a trigger.However, using a trigger means you have to make sure the trigger is written so it can handle multiple rows or fails if multiple rows are inserted. From past experience, I can tell you that as soon as you think everything is working correctly - someone will make a change to the application where they update or insert multiple rows.The question I have is why do you need this data in another database? There is probably a better way to solve your problem if we knew what issues you are trying to resolve by moving the data.Jeff |
|
|
|
dpnadam
Starting Member
29 Posts |
Posted - 2011-03-07 : 04:09:45
|
| Hi JeffThanks for your reply.The scenario is this. We have two applications - a 3rd party app we have no control over (apart from in the database of course), and another app that we have developed. The 3rd party app is acting as the 'master'. All the customers we deal with are held in this app database. Our application allows our teams to view particular groups of these customers. These groups are divided by region and managed by separate teams. So we will have a separate database and team for each region a customer belongs to. Within the 3rd party application is the ability to specify the region a customer belongs to.Whenever a customer is inserted/updated in the 3rd party app I need to move them to the appropriate region database depending on the chosen region. When a customer is updated the user will go into the details screen, make changes and then click a button to save the changes. They can't update multiple customers at once. The updates they perform will affect different tables in the 3rd party database, as specified in my first post. So the customers are inserted into the 3rd party db directly, not through a stored procedure.Does this make sense? Any questions, please ask away.Appreciate any advice.Thanks |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-03-07 : 08:18:51
|
| If you have a trigger on a table it will fire whenever you do an insert/update/delete so basically there is no way of preventing one trigger from firing based on the events of another trigger in another table. Why can't you just create 3 different procedures, one for each table? Alternatively you can create a parameter to this stored procedure that tells which trigger is firing, by hardcoding some custom parameter value. Then you can have some logic in the procedure depending on which table has been changed...- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
dpnadam
Starting Member
29 Posts |
Posted - 2011-03-07 : 09:08:21
|
| Hi LumbagoThanks for your suggestion. I had originally used a stored procedure for each table, but within the procedures I have an IF statement basically saying if the chosen region is equal to 'this' then send the data to this database. When it came to adding a new region and therefore a new database, I had to go into each stored procedure and update the IF statements. Therefore I thought if I have them all in one location theres only one place to update. I realise I could just use dynamic sql along with the chosen region to send the data, which would then not require updating for any new regions added, but I wanted to try and stay away from dynamic sql if possible. Unless it would be a viable option in this scenario? Further thoughts?Thanks. |
|
|
|
dpnadam
Starting Member
29 Posts |
Posted - 2011-03-09 : 06:30:50
|
| Would SQL Server Service Broker be a good alternative to this? |
|
|
|
|
Multiple Triggers call one stored procedure