SQL 2008 : Triggers - 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

General SQL Server Forums

New to SQL Server Programming

SQL 2008 : Triggers

Author

Topic

romanseaa
Starting Member

3 Posts

Posted - 2010-10-05 : 04:07:49

Hi,

I am new to SQL 2008.

For the past 3 days, I have been battling with TRIGGERS.

In my database, I have 2 tables : one is called "Details" (which includes : detailsid,personid,surname, firstname).

The second table is "Mainaddress" (addressid, personid, street, city).

These 2 tables share only one common attribute : personid

Next, I created a VIEW for these 2 tables, as follows :

SELECT dbo.mainaddress.street, dbo.mainaddress.city

FROM dbo.mainaddress

INNER JOIN dbo.Details ON dbo.mainaddress.personid = dbo.Details.personid

But, as we all know, it is not possible to manipulate more than one base table when using VIEWS.

To solve this problem, I decided to create TRIGGERS. I created 3 triggers : one each for INSERT, UPDATE and DELETE.

But, for some reason, I keep getting the error :

INVALID COLUMN NAME ("column name")

I have searched far and wide for a solution : forums, Google, etc, but without success.

For example : here is my INSERT trigger.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create trigger IdM_tunnukset_insert on IdM_tunnukset instead of insert

AS
BEGIN

SET NOCOUNT ON;

insert into mainaddress
select addressid,personid,street,city from inserted

insert into details
select details_id,personid,surname,firstname from inserted

END
GO

But, I kept getting the error :

INVALID COLUMN NAME addressid;
INVALID COLUMN NAME personid;

etc, etc, etc. For EVERY of my columns.

So, I decided to DECLARE the columns as follows :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create trigger dbo.IdM_tunnukset_insert on dbo.IdM_tunnukset instead of insert

AS
BEGIN

SET NOCOUNT ON;

declare @new_addressid nvarchar(15)
declare @new_personid nvarchar(10)
declare @new_street nvarchar(50)
declare @new_city nvarchar(20)
declare @new_surname nvarchar(50)
declare @new_firstname nvarchar(50)
declare @new_details_id nchar(12)

select @new_addressid=addressid from inserted
select @new_personid=personid from inserted)
select @new_street=street from inserted
select @new_city=city from inserted
select @new_surname=surname from inserted
select @new_firstname=firstname from inserted
select @new_details_id=details_id from inserted

insert into mainaddress
select addressid,personid,street,city from inserted

insert into details
select details_id,personid,surname,firstname from inserted

END
GO

But, still the error persists!

What am I doing wrong??

Any help would be greatly appreciated.Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-05 : 04:45:11

quote:
SELECT dbo.mainaddress.street, dbo.mainaddress.city

FROM dbo.mainaddress

INNER JOIN dbo.Details ON dbo.mainaddress.personid = dbo.Details.personid

I understand that you are inserting data through view but in view defination you have not included the columns which you are using inside the trigger
Ex: details_id,personid,surname,firstname

You need to have these columns and other columns used in trigger too.

romanseaa
Starting Member

3 Posts

Posted - 2010-10-05 : 06:39:57

Thank you very much, pk_bohra

that was a rather silly mistake on my part ))

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-05 : 06:48:33

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources