| Author |
 Topic  |
|
|
abyie
Starting Member
42 Posts |
 Posted - 10/17/2006 : 14:46:28
|
Hi,
I created two seprate triggers for insertion and updates from a view on a database A to to multiple tables on database B.But when I tried to test the trigger by inserting records on the view 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
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 10/17/2006 : 15:28:44
|
| Do you get any errors? |
 |
|
|
abyie
Starting Member
42 Posts |
Posted - 10/17/2006 : 15:31:32
|
| nopes when i created this trigger and ran it on the view, i didnt get any errors. |
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 10/17/2006 : 15:38:58
|
| Please post the view definition and the table definitions too. |
|
|
|
abyie
Starting Member
42 Posts |
Posted - 10/17/2006 : 15:55:31
|
Hi,
Here is my view definition and table defintions.Please let me know if you need any further info.
View Definition
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.SALESFORCETONIGELINSERTVIEW
AS
SELECT pt.Part_ID, c.CEO_ID__c, c.FirstName, c.LastName, A.Co_ID__c, A.Name, A.Industry, A.BillingCity, A.BillingPostalCode, A.BillingState, A.BillingStreet,
A.Borough__c, p.Job_Start_Date__c, p.Date__c AS Term_Date, p.Benefits__c, p.Bonded__c, p.Commute_time__c, p.Health_Benefits__c,
p.Health_Family__c, p.Health_Indiv__c, p.Hourly_Wage__c, p.Hours_Per_Week__c, p.Pension__c, p.Placement_End_Date__c,
p.Placement_Notes__c, p.Sick_Leave__c, p.Term_Code__c, p.Vacation__c, p.WOTC__c, O.Name AS Job_Title, p.Employer_Contact__c,
p.Assisted_By__c, p.SystemModstamp, c.Birthdate, c.Borough__c AS STATE, c.Company_ID__c, c.Contact_ID__c, c.Gender__c, c.HomePhone,
N.User_ID
FROM dbo.Placement__c p LEFT OUTER JOIN
dbo.Contact c ON c.Id = p.Participant__c LEFT OUTER JOIN
dbo.Opportunity O ON O.Id = p.Opportunity__c LEFT OUTER JOIN
dbo.Account A ON A.Id = O.AccountId LEFT OUTER JOIN
dbo.Users u ON p.OwnerId = u.Id LEFT OUTER JOIN
Main5.dbo.Part pt ON c.CEO_ID__c = pt.CEO_ID COLLATE database_default LEFT OUTER JOIN
Main5.dbo.NtoSUserLookup N ON u.Id = N.Id COLLATE database_default
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------
Part_Term Table Definiton
CREATE TABLE [Part_Term] (
[Date] [datetime] NOT NULL ,
[Part_ID] [int] NOT NULL ,
[Co_ID] [int] NULL ,
[Term_Code] [smallint] NULL ,
[User_ID] [int] NULL ,
[Comment_1] [char] (255) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Comment_2] [char] (255) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Comment_3] [char] (255) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Comment_4] [char] (255) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Wage] [float] NULL ,
[Title] [char] (25) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[DOT_Code] [int] NULL ,
[Train_Wage] [float] NULL ,
[Training_Hrs] [float] NULL ,
[Drug_Related] [tinyint] NULL ,
[h_ind] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[h_fam] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[sl] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[va] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[pention] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[bonded] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[tax_creadit] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[empowerment_zone] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[proof_ind] [char] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[benef_day] [datetime] NULL ,
[no_benefits] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[hours_per_week] [float] NULL ,
[commute_time_to_work] [int] NULL ,
[contract_start_date] [datetime] NULL ,
[contract_end_date] [datetime] NULL ,
[contact_id] [int] NULL ,
[timestamp] [datetime] NULL ,
[login_user_id] [int] NULL ,
[job_start_date] [datetime] NULL ,
[EAP_Services] [tinyint] NULL ,
[assisted_user_id] [int] NULL ,
[Last_Modified] [datetime] NULL CONSTRAINT [DF_Part_Term_Last_Modified] DEFAULT (getdate()),
[Facility] [int] NULL ,
[LSE_Completion_Date] [datetime] NULL ,
[LSE_Not_Required_Date] [datetime] NULL
) ON [PRIMARY]
GO
Thanks for your help
|
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 10/17/2006 : 17:47:03
|
It looks like it should work and the fact that you aren't seeing any errors is strange - if I try to add a row that violates anything in an underlying table I get an error on the view insert, it doesn't just hide the error.
One more thing you could post is the INSERT statement you're using.
Also, this may seem obvious, but I need to check, have you done a select directly on the Part_Term table to see if the row has been inserted? The view doesn't reference the table, so you won't see the inserted row when you select from the view. |
|
|
|
abyie
Starting Member
42 Posts |
Posted - 10/18/2006 : 01:46:22
|
Hi,
Thanks for your valuable input.It seems really strange as to why it is not even comin up with an error.
Here is the insert statement i am using to insert records from the view into the table.
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 |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 10/18/2006 : 04:09:24
|
I know this can be very foolish but just try and see if you are able to insert the records into your local database??? seems nothing wrong to me..
Chirag
http://chirikworld.blogspot.com/ |
|
|
|
abyie
Starting Member
42 Posts |
Posted - 10/18/2006 : 10:02:56
|
Hi,
I did try to insert records into the table of Main 4 database and it successfully inserted the records.So I don't think that there is any issue of inserting records.Also would like to know that right now, when a row is inserted into a view the trigger fires and updatest the base table.Is that fine as I want to update the base table not the view ? Also should I be using the path SALESFORCESBK.dbo.inserted instead of just inserted.Would that help as I am inserting records on a different database?
Thanks for your help
------
Abyie |
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 10/18/2006 : 11:20:28
|
quote:
Also would like to know that right now, when a row is inserted into a view the trigger fires and updatest the base table. Is that fine as I want to update the base table not the view ?
That's correct - you never update rows in a view, because there are no rows in a view, you're always updating the rows in the base table OR as in your case, with an INSTEAD OF trigger you can update rows in any table you like.
quote:
Also should I be using the path SALESFORCESBK.dbo.inserted instead of just inserted. Would that help as I am inserting records on a different database?
No. The inserted table doesn't exist in any database, it is a virtual table that SQL Server gives you that represents the rows that got inserted, so you must just refer to it as inserted.
I know which INSERT you're using in the trigger, but can you post the INSERT that you are using to insert into the view, something like
INSERT SALESFORCESBK.dbo.SALESFORCETONIGELINSERTVIEW (...) VALUES (...) |
|
|
|
abyie
Starting Member
42 Posts |
Posted - 10/18/2006 : 11:47:49
|
Hi,
Thanks for answering the questions.But here is actual query
that I am using to insert into the view
CREATE VIEW dbo.SALESFORCETONIGELINSERTVIEW
AS
SELECT pt.Part_ID, c.CEO_ID__c, c.FirstName, c.LastName, A.Co_ID__c, A.Name, A.Industry, A.BillingCity, A.BillingPostalCode, A.BillingState, A.BillingStreet,
A.Borough__c, p.Job_Start_Date__c, p.Date__c AS Term_Date, p.Benefits__c, p.Bonded__c, p.Commute_time__c, p.Health_Benefits__c,
p.Health_Family__c, p.Health_Indiv__c, p.Hourly_Wage__c, p.Hours_Per_Week__c, p.Pension__c, p.Placement_End_Date__c,
p.Placement_Notes__c, p.Sick_Leave__c, p.Term_Code__c, p.Vacation__c, p.WOTC__c, O.Name AS Job_Title, p.Employer_Contact__c,
p.Assisted_By__c, p.SystemModstamp, c.Birthdate, c.Borough__c AS STATE, c.Company_ID__c, c.Contact_ID__c, c.Gender__c, c.HomePhone,
N.User_ID
FROM dbo.Placement__c p LEFT OUTER JOIN
dbo.Contact c ON c.Id = p.Participant__c LEFT OUTER JOIN
dbo.Opportunity O ON O.Id = p.Opportunity__c LEFT OUTER JOIN
dbo.Account A ON A.Id = O.AccountId LEFT OUTER JOIN
dbo.Users u ON p.OwnerId = u.Id LEFT OUTER JOIN
Main5.dbo.Part pt ON c.CEO_ID__c = pt.CEO_ID COLLATE database_default LEFT OUTER JOIN
Main5.dbo.NtoSUserLookup N ON u.Id = N.Id COLLATE database_default
Thanks |
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 10/18/2006 : 12:44:12
|
No, that's the statement you use to create the view.
Now, you can SELECT from that view or you can INSERT into that view. You said no rows get inserted when you insert into the view, well, what statement do you run to INSERT rows into the view?
It will be an INSERT statement like
INSERT SALESFORCESBK.dbo.SALESFORCETONIGELINSERTVIEW (...) VALUES (...) |
|
|
|
abyie
Starting Member
42 Posts |
Posted - 10/18/2006 : 12:58:39
|
Hi,
I think I should have been a little more precise with my problem. The view I thought would automatically insert records when
any records are inserted into the base tables from which the view is defined.Because I tested it, when I inserted a records into the base table , it automatically inserted records into the view.So I do'nt think I need an insert statement to insert records from the base tables into the view.It would automatically select the records into view.
Thanks
---------
Abyie |
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 10/18/2006 : 13:05:46
|
OK, in that case it is your understanding of views that is the problem. Views do not have any data in them. They are basically just saved SELECT statements, so whenever you INSERT into a base table you aren't inserting into the view, but the row that you inserted into the base table will now show up in the view (assuming that the SELECT statement in the view isn't filtering out the row).
So view are - well, views, on the underlying base tables - whatever you do to the base table will ultimately affect all of the views on that base table. |
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 10/18/2006 : 13:11:00
|
| One more thing - in your view defintion (the CREATE VIEW statement) you aren't using the Part_Term table, therefore you will never see anything from that table through that view. |
|
|
|
abyie
Starting Member
42 Posts |
Posted - 10/18/2006 : 13:22:18
|
Hi,
Alright ..I c ur point..But still I can't figure out what should be the next step in order for me to insert records into the base table.Please can you help me out.
thanks
-----
abyie |
|
|
|
abyie
Starting Member
42 Posts |
Posted - 10/18/2006 : 13:32:48
|
Hi,
Alright ..I c ur point..But still I can't figure out what should be the next step in order for me to insert records into the base table.Please can you help me out.
thanks
-----
abyie |
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 10/18/2006 : 13:34:32
|
| Well, just run the INSERT statement that you already have (and/or write more INSERT statements if you need to insert into other tables) - I'm not sure what you want to do other than that? |
|
|
| |
Topic |
|
|
|
trigger help
Forum Locked
