July 1, 2014 at 10:07 am
I have a table called K2K01.dbo.cust
and another called K2KConceptActions.dbo.Actions(different database, same server)
The K2K01.dbo.cust table is part of an ERP system.
The K2KConceptActions.dbo.Actions table is a queuing table for a .net based web service and client app that will reside on the server and monitor the K2KConceptActions.dbo.Actions for updated and inserted records and then connect to another server via its web service (hence client app) and deliver a payload of updated or new data.
The K2KConceptActions.dbo.Actions table is traffic central for .net app to monitor inserts and updates for the customer master (K2K##.dbo.cust) and item master tables (K2K##.dbo.item) across 4 databases (K2K01, K2K02, K2K03, K2K04)
SO, what is inserted into the K2KConceptActions.dbo.Actions table is: (these are the columns)
Action - UPDATE or INSERT
Database - which db the change came from
Source - CustomerMaster or ItemMaster
KeyData - the key that the .net app will look up the record out of the database and table
Also there is an INT incrementor column called "UID"
I need help writing the first trigger. I can get the rest once I see how to write the first one.
So assuming the first trigger is an AFTER trigger on the insert of a new customer master record or multiple inserts within the transaction,my pseudo code for the trigger would be:
Create the trigger K2K01.dbo.InsertCustomerInsertAction on K2K01.dbo.cust
FOR INSERT
BEGIN
IF @@rowcount = 0 RETURN
for each record inserted into (call the record I) ,
create a record in such that:
M2MConceptActions.dbo.Actions.Action ="INSERT"
M2MConceptActions.dbo.Actions.Database="K2K01"
M2MConceptActions.dbo.Actions.source = "CustomerMaster "
M2MConceptActions.dbo.Actions.KeyData= i.CustomerID
Any help would be appreciated!
Thnx!!
Harold G
July 1, 2014 at 10:22 am
A trigger like this should work:
CREATE TRIGGER K2K01.dbo.InsertCustomerInsertAction ON K2K01.dbo.cust
FOR INSERT
AS
BEGIN;
IF @@rowcount = 0
BEGIN;
RETURN;
END;
INSERT INTO M2MConceptActions.dbo.Actions
(
[ACTION],
[DATABASE],
[SOURCE],
KeyData
)
SELECT
'Insert' AS [Action],
DB_NAME(DB_ID()) AS [database],
'CustomerMaster' AS [Source],
i.CustomerID
FROM
inserted;
END;
The thing you have to be aware for when doing something like this is permissions. You either need cross database ownership chaining enabled, have the user executing the initial insert have permissions on the shared table, use certificates or impersonation. You should read this article by Erland Sommerskag for the permissions piece.
I'd also look into using Service Broker for this. It might be a better solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 1, 2014 at 10:30 am
Thnx so much!!
I looked at service broker briefly, so I will revisit. In the mean time thnx again for the reply!!
July 1, 2014 at 12:01 pm
Jack,
Can the service broker tell exactly which record was updated or inserted? or somehow handover a reference to the changed records to a .net app?
Harold
July 2, 2014 at 7:40 am
I'm not a Service Broker expert, but essentially what you are doing is creating a queue by putting that data in another database. Service Broker may help with the security issue because I'd use the trigger to insert into a local Service Broker Queue which then would start a conversation with a queue in the target database which picks it up and processes it. So you'd define a message with the data you need.
SB might be overkill for your situation, but the pluses would be:
<ul>
<li>No need for cross database ownership chaining or security issues with inserting data across databases</li>
<li>It would also allow you to move the target database to another server in the future and you'd only have to change the Route to point to the new server with the service broker queue destination. My terminology may not be correct, but that's the general idea.</li>
<li>You get asynchronous processing. Once the trigger dumps the message in the local queue control is returned to the application. If the insert into the destination table fails for some reason the initial transaction doesn't fail. You still have the message and can fix the problem with the data or the insert process and re-process the transaction.</li>
</ul>
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply