SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 trigger help
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

abyie
Starting Member

42 Posts

Posted - 10/17/2006 :  14:46:28  Show Profile
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  Show Profile
Do you get any errors?
Go to Top of Page

abyie
Starting Member

42 Posts

Posted - 10/17/2006 :  15:31:32  Show Profile
nopes when i created this trigger and ran it on the view, i didnt get any errors.
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/17/2006 :  15:38:58  Show Profile
Please post the view definition and the table definitions too.
Go to Top of Page

abyie
Starting Member

42 Posts

Posted - 10/17/2006 :  15:55:31  Show Profile
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/17/2006 :  17:47:03  Show Profile
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.
Go to Top of Page

abyie
Starting Member

42 Posts

Posted - 10/18/2006 :  01:46:22  Show Profile
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
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 10/18/2006 :  04:09:24  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
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/
Go to Top of Page

abyie
Starting Member

42 Posts

Posted - 10/18/2006 :  10:02:56  Show Profile
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/18/2006 :  11:20:28  Show Profile
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 (...)
Go to Top of Page

abyie
Starting Member

42 Posts

Posted - 10/18/2006 :  11:47:49  Show Profile
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/18/2006 :  12:44:12  Show Profile
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 (...)
Go to Top of Page

abyie
Starting Member

42 Posts

Posted - 10/18/2006 :  12:58:39  Show Profile
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/18/2006 :  13:05:46  Show Profile
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.
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/18/2006 :  13:11:00  Show Profile
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.
Go to Top of Page

abyie
Starting Member

42 Posts

Posted - 10/18/2006 :  13:22:18  Show Profile
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
Go to Top of Page

abyie
Starting Member

42 Posts

Posted - 10/18/2006 :  13:32:48  Show Profile
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/18/2006 :  13:34:32  Show Profile
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000