from inserted question - 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 2005 Forums

Transact-SQL (2005)

from inserted question

Author

Topic

phishncode
Starting Member

2 Posts

Posted - 2008-03-20 : 21:27:04

I have created an updatabable view.

It uses the INSTEAD OF INSERT clause to fire because there are multiple base tables.

lets say the view has 6 fields
field1,field2, field3, field4, field5, field6

to get the values from the inserted record I have to declare the variables first

declare field1 varchar(50),field2 varchar(50), field3 varchar(50), field4 varchar(50), field5 varchar(50), field6 varchar(50)

I then have to assign the information to these variables

select @field1 = field1,@field2 = field2,@field3 = field3, @field4 = field4,@field5 = field5,@field6 = field6 from inserted

I can then use these values to execute store procedures

exec sp1 @field1, @field2, @field3
exec sp2 @field4, @field5, @field6

is there anyway to just do this

exec sp1 inserted.field1, inserted.field2, inserted.field3
exec sp2 inserted.field4, inserted.field5, inserted.field6

I tried
exec spec select field1, field2, field3 from inserted and it did not give me an error message BUT it did not work

I would like to use my stored procedures for data insertion

I appreciate your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-20 : 22:02:28

You must pass values to parameters. You will need to make quite a bit of changes to your stored procedure to do what you want. But we will need more information in order to help. We'll need to see your stored procedure and also show us via a data example what needs to happen.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

phishncode
Starting Member

2 Posts

Posted - 2008-03-20 : 22:41:22

I need to have an updatable view it has to be this way to simplify data access from an access front end.

there is an address table which is accessed from many different tables one of which is the vendor table.

USE [CFLRPS]
GO
/****** Object: Table [dbo].[vendor] Script Date: 03/20/2008 19:39:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[vendor](
[vendor_id] [int] IDENTITY(1,1) NOT NULL,
[vendor_nm] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vendor_creation_user_nm] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vendor_billing_addr_id] [int] NULL,
[vendor_creation_dt] [datetime] NULL CONSTRAINT [DF_vendor_vendor_creation_dt] DEFAULT (getdate()),
[vendor_abbr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vendor_cntc_nm] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vendor_mailing_addr_id] [int] NULL,
[vendor_notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vendor_cd] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_vendor] PRIMARY KEY CLUSTERED
(
[vendor_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[vendor] WITH CHECK ADD CONSTRAINT [FK_vendor_addr] FOREIGN KEY([vendor_mailing_addr_id])
REFERENCES [dbo].[addr] ([addr_id])
GO
ALTER TABLE [dbo].[vendor] CHECK CONSTRAINT [FK_vendor_addr]

this is the address table

USE [CFLRPS]
GO
/****** Object: Table [dbo].[addr] Script Date: 03/20/2008 19:39:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[addr](
[addr_id] [int] IDENTITY(1,1) NOT NULL,
[addr_nm] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_city_nm] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr3] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[zip_cd] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_ph_no] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_fax_no] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[st_id] [int] NULL,
[addr_creation_dt] [datetime] NULL CONSTRAINT [DF_addr_addr_creation_dt] DEFAULT (getdate()),
[addr_creation_user_nm] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_addr] PRIMARY KEY CLUSTERED
(
[addr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[addr] WITH CHECK ADD CONSTRAINT [FK_addr_st] FOREIGN KEY([st_id])
REFERENCES [dbo].[st] ([st_id])
GO
ALTER TABLE [dbo].[addr] CHECK CONSTRAINT [FK_addr_st]

here is the view

SELECT billing_addr.addr_nm AS billing_addr_nm, billing_addr.addr_city_nm AS billing_addr_city_nm, billing_addr.addr1 AS billing_addr_addr1,
billing_addr.addr2 AS billing_addr_addr2, billing_addr.addr3 AS billing_addr_addr3, billing_addr.zip_cd AS billing_addr_zip_cd,
billing_addr.addr_ph_no AS billing_addr_ph_no, billing_addr.addr_fax_no AS billing_addr_fax_no, mailing_addr.addr_nm AS mailing_addr_nm,
mailing_addr.addr_city_nm AS mailing_addr_city_nm, mailing_addr.addr1 AS mailing_addr_addr1, mailing_addr.addr2 AS mailing_addr_addr2,
mailing_addr.addr3 AS mailing_addr_addr3, mailing_addr.zip_cd AS mailing_addr_zip_cd, mailing_addr.addr_ph_no AS mailing_addr_ph_no,
mailing_addr.addr_fax_no AS mailing_addr_fax_no, dbo.vendor.vendor_nm, dbo.vendor.vendor_abbr, dbo.vendor.vendor_notes, dbo.vendor.vendor_cd,
dbo.vendor.vendor_id, dbo.vendor.vendor_mailing_addr_id, dbo.vendor.vendor_billing_addr_id, dbo.vendor.vendor_cntc_nm
FROM dbo.vendor INNER JOIN
dbo.addr AS billing_addr ON dbo.vendor.vendor_billing_addr_id = billing_addr.addr_id INNER JOIN
dbo.addr AS mailing_addr ON dbo.vendor.vendor_mailing_addr_id = mailing_addr.addr_id

and the createaddr stored procedure

USE [CFLRPS]
GO
/****** Object: StoredProcedure [dbo].[createaddr] Script Date: 03/20/2008 19:35:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[createaddr]
-- Add the parameters for the stored procedure here
@addr_nm varchar(50) = NULL,
@addr_city_nm varchar(50) = NULL,
@addr1 varchar(50) = NULL,
@addr2 varchar(50) = NULL,
@addr3 varchar(50) = NULL,
@zip_cd varchar(50) = NULL,
@addr_ph_no varchar(20) = NULL,
@addr_fax_no varchar(20) = NULL,
@st_id int = NULL
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO [CFLRPS].[dbo].[addr]
([addr_nm]
,[addr_city_nm]
,[addr1]
,[addr2]
,[addr3]
,[zip_cd]
,[addr_ph_no]
,[addr_fax_no]
,[st_id])
VALUES
(@addr_nm,
@addr_city_nm,
@addr1,
@addr2,
@addr3,
@zip_cd,
@addr_ph_no,
@addr_fax_no,
@st_id)
-- Insert statements for procedure here
RETURN @@IDENTITY
END

and here is the createvendor stored procedure

USE [CFLRPS]
GO
/****** Object: StoredProcedure [dbo].[createvendor] Script Date: 03/20/2008 19:36:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[createvendor]
-- Add the parameters for the stored procedure here
@vendor_nm varchar(50) = NULL,
@vendor_billing_addr_id int = NULL,
@vendor_abbr varchar(50) = NULL,
@vendor_cntc_nm varchar(50) = NULL,
@vendor_mailing_addr_id int = NULL,
@vendor_notes text = NULL,
@vendor_cd varchar(4) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [CFLRPS].[dbo].[vendor]
([vendor_nm]
,[vendor_billing_addr_id]
,[vendor_abbr]
,[vendor_cntc_nm]
,[vendor_mailing_addr_id]
,[vendor_notes]
,[vendor_cd])
VALUES
(@vendor_nm,
@vendor_billing_addr_id,
@vendor_abbr,
@vendor_cntc_nm,
@vendor_mailing_addr_id,
@vendor_notes,
@vendor_cd)
RETURN @@IDENTITY
END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER viewvendorinsteadofinsert
ON dbo.viewvendor instead of insert

AS
DECLARE
@billing_addr_id int,
@mailing_addr_id int,
@vendor_id int
declare @billing_addr_nm varchar(50), @billing_addr_city_nm varchar(50), @billing_addr_addr1 varchar(50), @billing_addr_addr2 varchar(50), @billing_addr_addr3 varchar(50), @billing_addr_zip_cd varchar(50), @billing_addr_ph_no varchar(20), @billing_addr_fax_no varchar(20),@billing_addr_st_id int
@mailing_addr_nm varchar(50), @mailing_addr_city_nm varchar(50), @mailing_addr_addr1 varchar(50), @mailing_addr_addr2 varchar(50), @mailing_addr_addr3 varchar(50), @mailing_addr_zip_cd varchar(50), @mailing_addr_ph_no varchar(20), @mailing_addr_fax_no varchar(20),@mailing_addr_st_id int

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--declare records cursor for select * from inserted

select @billing_addr_nm = billing_addr_nm,@billing_addr_city_nm = billing_addr_city_nm , @billing_addr_addr1 = billing_addr_addr1,@billing_addr_addr2 = billing_addr_addr2 , @billing_addr_addr3 = billing_addr_addr3,@billing_addr_zip_cd = billing_addr_zip_cd, @billing_addr_ph_no = billing_addr_ph_no, @billing_addr_fax_no = billing_addr_fax_no from inserted
select @mailing_addr_nm = mailing_addr_nm,@mailing_addr_city_nm = mailing_addr_city_nm , @mailing_addr_addr1 = mailing_addr_addr1,@mailing_addr_addr2 = mailing_addr_addr2 , @mailing_addr_addr3 = mailing_addr_addr3,@mailing_addr_zip_cd = mailing_addr_zip_cd, @mailing_addr_ph_no = mailing_addr_ph_no, @mailing_addr_fax_no = mailing_addr_fax_no from inserted
execute @billing_addr_id = createaddr @billing_addr_nm, @billing_addr_city_nm, @billing_addr_addr1, @billing_addr_addr2, @billing_addr_addr3,@billing_addr_zip_cd,@billing_addr_ph_no,@billing_addr_fax_no,@billing_addr_st_id
--execute @mailing_addr_id = createaddr select mailing_addr_nm, mailing_addr_city_nm,mailing_addr_addr1,mailing_addr_addr2,mailing_addr_addr3,mailing_addr_zip_cd,mailing_addr_ph_no,mailing_addr_fax_no from inserted

execute @mailing_addr_id = createaddr @mailing_addr_nm, @mailing_addr_city_nm, @mailing_addr_addr1, @mailing_addr_addr2, @mailing_addr_addr3,@mailing_addr_zip_cd,@mailing_addr_ph_no,@mailing_addr_fax_no,@mailing_addr_st_id

-- This is what I would like here to simplify creation

execute @vendor_id = createvendor select vendor_nm, @billing_addr_id,vendor_abbr, vendor_cntc_nm,@mailing_addr_id,vendor_notes, vendor_cd from inserted

--records.billing_addr_nm, records.billing_addr_city_nm,records.billing_addr_addr1,records.billing_addr_addr2,records.billing_addr_addr3,records.billing_addr_zip_cd,records.billing_addr_ph_no,records.billing_addr_fax_no
-- Insert statements for trigger here

END
GO

I appreciate your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources