Trigger Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger Issue

Author

Topic

abyie
Starting Member

42 Posts

Posted - 2006-10-19 : 11:27:49

Hi ALL,

Scenario
Here is the scenario of my case.We have a Salesforce web application from which we import data into a Sql Server database A using DBAmp Pro tool.Also we have another inhouse application which stores all its data on another database B.The thing we are tryin to achieve is some of records(i.e. placemnt information)that comes from Salesforce application which is residing on Database A has to be inserted or updated on the tables residing on Database B.
The data on Database A will be daily refreshed using a sql agent.

Action Taken
1. The first thing we did was to create a View 1 on Database A(i.e. Salesforce data) which has all the fields that needs to be insered or updated on database B.There are certain joins as the fields are stored in multiple table.

2. I created two seprate triggers for insertion and updates from a view on a database A to to multiple tables on database B(i.e Part_Term and Follow_Up). But when I tried to test the trigger by running the refresh proceudure on Database A which would referesh data on Database A, the trigger did'nt do anything.Can anybody help me on this?

Insertion Trigger Syntax
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER TRIGGER NigelInsert
ON SALESFORCESBK.dbo.SALESFORCETONIGELINSERTVIEW
INSTEAD OF INSERT
AS
---------------------------------------------------------------
BEGIN
INSERT INTO Main4.dbo.Part_Term
(Date,
Part_ID,
Co_ID,
User_ID,
Wage,
Title,
h_ind,
h_fam,
sl,
va,
pention,
bonded,
no_benefits,
hours_per_week,
commute_time_to_work,
job_start_date,
contact_id
)

Select
Term_Date,
Part_ID,
Co_ID__c,
User_ID,
Hourly_Wage__c,
Job_Title ,
Health_Indiv__c,
Health_Family__c,
Sick_Leave__c,
Vacation__c,
Pension__c,
Bonded__c,
Convert(char,Benefits__c),
Hours_Per_Week__c,
Commute_time__c,
Job_Start_Date__c,
Contact_ID__c
from inserted

END
--------------------------------------------------------------------

BEGIN
INSERT INTO
Main4.dbo.Follow_Up
(Part_ID,
Follow_Date,
Reason_id,
Still_Employed,
Follow_Days,
Term_Date
)
SELECT
Part_ID,
DATEADD(DD,30,Job_Start_Date__c),
100,
NULL,
30,
Term_Date
from inserted

END
--------------------------------------------------------------------
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Your help wil be greatly appreciated.

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 12:57:24

abyie you still don't get what I'm trying to tell you about views. If you create a trigger on a view, that trigger will not run, ever, unless you use an INSERT or UPDATE statement on the view, like

INSERT SALESFORCESBK.dbo.SALESFORCETONIGELINSERTVIEW ....
or
UPDATE SALESFORCESBK.dbo.SALESFORCETONIGELINSERTVIEW ....

It sounds though like you want to INSERT directly into the table in SALESFORCESBK - so then the trigger on the view will not run. So in that case you need to SELECT from the view and use the results to INSERT into the table in database B, like this

INSERT Main4.dbo.Part_Term (Date,
Part_ID,
Co_ID,
User_ID,
Wage,
Title,
h_ind,
h_fam,
sl,
va,
pention,
bonded,
no_benefits,
hours_per_week,
commute_time_to_work,
job_start_date,
contact_id
)
SELECT Term_Date,
Part_ID,
Co_ID__c,
User_ID,
Hourly_Wage__c,
Job_Title ,
Health_Indiv__c,
Health_Family__c,
Sick_Leave__c,
Vacation__c,
Pension__c,
Bonded__c,
Convert(char,Benefits__c),
Hours_Per_Week__c,
Commute_time__c,
Job_Start_Date__c,
Contact_ID__c
FROM SALESFORCESBK.dbo.SALESFORCETONIGELINSERTVIEW

And now, you must either run this manually, or you must run it from a trigger on the table that the view is built on in SALESFORCESBK.

I've specifically shown for INSERTs, but the same applies to UPDATEs or DELETEs.

abyie
Starting Member

42 Posts

Posted - 2006-10-19 : 15:25:28

Thanks for your reply .first of all sry that I didn't understand the logic last time around. But if I am analyzing it correctly from what i gathered is now in the trigger for the insert statement, I should be using
Insert Part_Term(
..)
Values (
..
)
FROM SALESFORCESBK.dbo.SALESFORCETONIGELINSERTVIEW instead of
inserted right ? And this now will have to be a manual process where I have to every run the trigger to update teh table?

Please let me know if this is correct
Do you know if there is a better way of doing it?

Thanks for your help
-----------------
abyie

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 15:40:45

I said it could be manual OR based on a trigger, but that if it based on a trigger, then the trigger needs to be on the table, not the view.

You cannot run a trigger - it runs automatically based on an action taking place. So for example if you create an INSERT TRIGGER on a table, then it runs when you INSERT into that table; or if you create an UPDATE TRIGGER on a table, then it runs when you UPDATE that table.

The data that caused the trigger to run is available inside the trigger in the inserted and deleted tables, so if you want to use only the data that is causing the trigger to run, then use those tables, but you can use any other data from inside the trigger too if you want to.

I guess the real question is - do you want to automatically update the data in database B every time database A changes (inserts, updates and deletes) or do you want to just copy some of the data from database A to database B as a regular operation, for example at the end of the day?

abyie
Starting Member

42 Posts

Posted - 2006-10-19 : 15:57:45

Ideally we want to automatically update the data on the tables in database B every time tables in database A changes (inserts, updates and deletes)

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 16:01:57

OK, then do you want exact copies on the other server (in which case you should probably do it with replication and forget about creating triggers), or do you want to query the data on the one server and copy filtered and aggregated data to the other server (in which case triggers would be the way to go)?

abyie
Starting Member

42 Posts

Posted - 2006-10-20 : 01:11:01

Hi,
Thanks for your reply.Well what we are tryin to achieve is that when a new placement record is created on Salesforce Application it gets stored on Placement table on Database A.so as soon as the record is inserted into Placement table, the records should be inserted
into Part_term table on Database B.As the placement record comes from more than one table, that is why i had to create a view otherwise i would have jsut created the trigger on the table.

Cheers
----------
Abyie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources