trigger help - 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

Old Forums

CLOSED - General SQL Server

trigger help

Author

Topic

abyie
Starting Member

42 Posts

Posted - 2006-10-17 : 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
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-17 : 15:28:44

Do you get any errors?

abyie
Starting Member

42 Posts

Posted - 2006-10-17 : 15:31:32

nopes when i created this trigger and ran it on the view, i didnt get any errors.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-17 : 15:38:58

Please post the view definition and the table definitions too.

abyie
Starting Member

42 Posts

Posted - 2006-10-17 : 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
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-17 : 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 - 2006-10-18 : 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
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-18 : 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 - 2006-10-18 : 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
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 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 - 2006-10-18 : 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
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 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 - 2006-10-18 : 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
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 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
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 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 - 2006-10-18 : 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 - 2006-10-18 : 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
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources