AndreyZ wrote:Please specify the script to create your view and tables that are used by the view.
That is DDl script for View. I cut some code that doesn't affect the heart of the problem (...)
Code: Select all
CREATE VIEW dbo.VInsurances
WITH SCHEMABINDING
AS
select
(...)
,0 as Transport
,i.FVehicle TransportGUID
,CAST(v.FGarageNumber as nvarchar(128))+ ' '+ m.FName + ' ' + vn.FNumber as TransportInfo
from
dbo.TVehicleInsurances i join dbo.TInsuranceAgents a on i.FInsuranceAgent=a.FGUID
join dbo.TInsuranceTypes t on i.FInsuranceType=t.FGUID
join dbo.TVehicles v on i.FVehicle=v.FGUID
join dbo.TVehicleModels m on v.FVehicleModel=m.FGUID
join dbo.VVehicleIDNumbers vn on vn.FVehicle=v.FGUID
union all
select
(...)
,1 as Transport
,i.FTrackTrailer TransportGUID
,m.FName + ' ' + vn.FNumber as TransportInfo
from
dbo.TTrackTrailerInsurances i join dbo.TInsuranceAgents a on i.FInsuranceAgent=a.FGUID
join dbo.TInsuranceTypes t on i.FInsuranceType=t.FGUID
join dbo.TTruckTrailers tt on i.FTrackTrailer=tt.FGUID
join dbo.TTruckTrailerModels m on tt.FTruckTrailerModel=m.FGUID
join dbo.VTruckTrailerIDNumbers vn on vn.FTrackTrailer=tt.FGUID
union all
(...)
GO
As you can see, field "Transport" is calculated.
DDl script for Trigger Instead of:
Code: Select all
CREATE TRIGGER [dbo].[VInsurances_tri] ON [dbo].[VInsurances]
WITH EXECUTE AS CALLER
INSTEAD OF INSERT
AS
declare @code int
BEGIN
set @code= (select i.Transport from Inserted i)
if @code=0
Insert into TVehicleInsurances(FGUID,FNumber,FStart,FEnd,FInsuranceType,FInsuranceAgent,FVehicle)
select
i.FGUID
,i.FNumber
,i.FStart
,i.FEnd
,i.FInsuranceType
,i.FInsuranceAgent
,i.TransportGUID
from
Inserted i
else
if @code=1
Insert into TTrackTrailerInsurances(FGUID,FNumber,FStart,FEnd,FInsuranceType,FInsuranceAgent,FTrackTrailer)
select
i.FGUID
,i.FNumber
,i.FStart
,i.FEnd
,i.FInsuranceType
,i.FInsuranceAgent
,i.TransportGUID
from
Inserted i
else
(...)
Field “Transport” helps to determine table for inserting.
TMsStroreproc update sql scripts that I set manually (and it’s works!)
Code: Select all
INSERT INTO VInsurances
(FGUID, FNumber, FStart, FEnd, FInsuranceAgent, FInsuranceType, Transport, TransportGUID)
VALUES
(:FGUID, :FNumber, :FStart, :FEnd, :FInsuranceAgent, :FInsuranceType, :Transport, :TransportGUID)
And finally, SQL script, that automatically generated by component (cut from SQL Server Profiler):
Code: Select all
exec sp_executesql N'INSERT INTO VInsurances
(FGUID, FNumber, FStart, FEnd, FInsuranceAgent, FInsuranceType, TransportGUID)
VALUES
(@P1, @P2, @P3, @P4, @P5, @P6, @P7)',N'@P1 uniqueidentifier,@P2 nvarchar(4000),@P3 datetime2(7),@P4 datetime2(7),@P5 uniqueidentifier,@P6 uniqueidentifier,@P7 uniqueidentifier','8A9FEC8E-4D8F-4ABE-A154-3B5494EA0941',N'909090','2012-05-17 00:00:00','2013-05-18 00:00:00','A0A3C8D9-A1C9-4C7F-BBC1-664D6C563C9E','0A742324-2C9F-4DFA-8204-CC1CABA57F3B','D3C579AD-22FF-4E1D-BDC1-09E93325BFEE'
Field "Transport" doesn't exists!
There are several tables including into the View. That’s why I don’t post their code here. But if it’s necessary, I can send code by e-mail.