我有两个页面.第二个页面的一列数据是第一个页面的第一列数据,就是当第一页面增加一条数据时,第二页面也增加,删除时也对应删除,触发器在Inn_GemaltoLocations 写好了,如下:也就是说我在Inn_GemaltoLocations 增加,删除数据,在Inn_Industrial_PlantLayout 也就增加删除
//删除
CREATE TRIGGER dbo.Inn_GemaltoLocations_delete
ON dbo.Inn_GemaltoLocations
FOR Delete
AS
Declare @GL_ID NVARCHAR(50)
SELECT @GL_ID=GL_ID FROM deleted
begin
delete from Inn_Industrial_PlantLayout where GL_ID= @GL_ID
delete from Inn_GL_MajorCustomers where GL_ID=@GL_ID
end
// 插入
CREATE TRIGGER dbo.Inn_GemaltoLocations_insert
ON dbo.Inn_GemaltoLocations
FOR INSERT
AS
Declare @GL_ID NVARCHAR(50)
SELECT @GL_ID=GL_ID FROM inserted
begin
insert into Inn_Industrial_PlantLayout(PlantSite_id,GL_ID) values(NewID(),@GL_ID)
end
可是我现在更新到另一台机上时,更新第一页面的数据时(更新数据时用的脚本更新,具体脚本是先disable触发器,在插入,修改,删除,然后在enable触发器),我发现第二个页面上有数据,但是数据库却没有这个id郁闷.也就是说Inn_GemaltoLocations 和Inn_Industrial_PlantLayout 数据数量不一样,所以在修改对应值时提示id不存在.但是页面上怎么有的啊??崩溃,这是我的xml配置数据库的地方:如下:
<Dataset>
<id>dataset_list</id>
<conn_id>connstr1</conn_id>
<commandText>select p.*,n.GL_Name from Inn_GemaltoLocations n left join Inn_Industrial_PlantLayout p on n.GL_ID=p.GL_ID where n.GL_Name like '%'+?+'%' order by n.GL_Name</commandText>
<Parameters>
<Parameter>
<name>@GL_Name</name>
<dbType>string</dbType>
<queryValueExpression>document.all.edt_query_plant_name.value</queryValueExpression>
</Parameter>
</Parameters>
<commandType>text</commandType>
<tableName>inn_industrial_plantlayout</tableName>
<keyFields>plantsite_id</keyFields>
<Fields>
<Field>
<name>PlantSite_id</name>
<tag>[GUID]</tag>
<notNull>true</notNull>
</Field>
//删除
CREATE TRIGGER dbo.Inn_GemaltoLocations_delete
ON dbo.Inn_GemaltoLocations
FOR Delete
AS
Declare @GL_ID NVARCHAR(50)
SELECT @GL_ID=GL_ID FROM deleted
begin
delete from Inn_Industrial_PlantLayout where GL_ID= @GL_ID
delete from Inn_GL_MajorCustomers where GL_ID=@GL_ID
end
// 插入
CREATE TRIGGER dbo.Inn_GemaltoLocations_insert
ON dbo.Inn_GemaltoLocations
FOR INSERT
AS
Declare @GL_ID NVARCHAR(50)
SELECT @GL_ID=GL_ID FROM inserted
begin
insert into Inn_Industrial_PlantLayout(PlantSite_id,GL_ID) values(NewID(),@GL_ID)
end
可是我现在更新到另一台机上时,更新第一页面的数据时(更新数据时用的脚本更新,具体脚本是先disable触发器,在插入,修改,删除,然后在enable触发器),我发现第二个页面上有数据,但是数据库却没有这个id郁闷.也就是说Inn_GemaltoLocations 和Inn_Industrial_PlantLayout 数据数量不一样,所以在修改对应值时提示id不存在.但是页面上怎么有的啊??崩溃,这是我的xml配置数据库的地方:如下:
<Dataset>
<id>dataset_list</id>
<conn_id>connstr1</conn_id>
<commandText>select p.*,n.GL_Name from Inn_GemaltoLocations n left join Inn_Industrial_PlantLayout p on n.GL_ID=p.GL_ID where n.GL_Name like '%'+?+'%' order by n.GL_Name</commandText>
<Parameters>
<Parameter>
<name>@GL_Name</name>
<dbType>string</dbType>
<queryValueExpression>document.all.edt_query_plant_name.value</queryValueExpression>
</Parameter>
</Parameters>
<commandType>text</commandType>
<tableName>inn_industrial_plantlayout</tableName>
<keyFields>plantsite_id</keyFields>
<Fields>
<Field>
<name>PlantSite_id</name>
<tag>[GUID]</tag>
<notNull>true</notNull>
</Field>
CREATE TRIGGER dbo.Inn_GemaltoLocations_delete
ON dbo.Inn_GemaltoLocations
FOR Delete
AS
begin
delete a from Inn_Industrial_PlantLayout a inner join deleted b on a.GL_ID= b.GL_ID
delete a from Inn_GL_MajorCustomers a inner join deleted b on a.GL_ID= b.GL_ID
end
// 插入
CREATE TRIGGER dbo.Inn_GemaltoLocations_insert
ON dbo.Inn_GemaltoLocations
FOR INSERT
AS
begin
insert into Inn_Industrial_PlantLayout(PlantSite_id,GL_ID) select NewID(),GL_ID from inserted
end
不要在触发器里面实现,
不好控制.
还有就是,
貌似没有理解你的意思,
先disable的目的是不插入重复的数据.先做好更新操作,然后在enable
你是关联新增和删除的,为啥要disable触发器啦...
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GOIF @@TRANCOUNT = 1
ALTER TABLE [dbo].[Inn_GemaltoLocations] DISABLE TRIGGER [Inn_GemaltoLocations_delete]
GOIF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GOIF @@TRANCOUNT = 1
ALTER TABLE [dbo].[Inn_GemaltoLocations] DISABLE TRIGGER [Inn_GemaltoLocations_insert]
GOIF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GOIF @@TRANCOUNT = 1
COMMIT TRANSACTION
GO
BEGIN TRANSACTIONINSERT INTO [dbo].[Inn_GemaltoLocations]([GL_ID], [GL_Name], [GL_Code], [GL_City_ID], [GL_Share], [GL_PartnerName], [GL_ActualizationDate], [AccessMap], [CommChampion_id], [Director_ID], [GL_Fax], [GL_MFGProCode], [GL_State], [GL_Street], [GL_Tel], [GL_ZIP], [ToolsChampion_id]) VALUES (N'5a647bb0-79c9-4e54-b8c9-b4bd0b7041f2', N'Tianjin', N'TJI', N'b9b53e7c-eb31-49e3-8fce-8e7fb0430252', 100, NULL, '20071219 17:31:24.000', NULL, NULL, N'2dc4f2d8-7852-4f15-8e60-a0505a8054d8', NULL, N'CH76', NULL, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Inn_GemaltoLocations]([GL_ID], [GL_Name], [GL_Code], [GL_City_ID], [GL_Share], [GL_PartnerName], [GL_ActualizationDate], [AccessMap], [CommChampion_id], [Director_ID], [GL_Fax], [GL_MFGProCode], [GL_State], [GL_Street], [GL_Tel], [GL_ZIP], [ToolsChampion_id]) VALUES (N'76315036-a8c5-42e6-951a-6176e4bed072', N'SAIT', N'SAIT', N'3a83f5e6-4a14-4f8e-b45f-894d52c4a463', NULL, NULL, '20071219 17:28:57.000', NULL, NULL, N'17502b64-de42-472f-9152-8ad97860dccc', NULL, N'CN08', NULL, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Inn_GemaltoLocations]([GL_ID], [GL_Name], [GL_Code], [GL_City_ID], [GL_Share], [GL_PartnerName], [GL_ActualizationDate], [AccessMap], [CommChampion_id], [Director_ID], [GL_Fax], [GL_MFGProCode], [GL_State], [GL_Street], [GL_Tel], [GL_ZIP], [ToolsChampion_id]) VALUES (N'861f4d22-f157-40a9-be41-0964529d98ab', N'Kuala Lumpur', N'KUA', N'7e989487-cff3-4dfe-a5e7-9161807f5712', 100, NULL, '20071219 13:17:25.000', NULL, NULL, NULL, NULL, N'KUA', NULL, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Inn_GemaltoLocations]([GL_ID], [GL_Name], [GL_Code], [GL_City_ID], [GL_Share], [GL_PartnerName], [GL_ActualizationDate], [AccessMap], [CommChampion_id], [Director_ID], [GL_Fax], [GL_MFGProCode], [GL_State], [GL_Street], [GL_Tel], [GL_ZIP], [ToolsChampion_id]) VALUES (N'948885a0-eb19-4271-b80f-5e70b7fb9f18', N'Hong-Kong', N'HK', N'ac670485-be15-4bac-b83c-3dd89d55ca1d', NULL, NULL, '20080514 15:04:02.000', NULL, NULL, NULL, NULL, N'CN09', NULL, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Inn_GemaltoLocations]([GL_ID], [GL_Name], [GL_Code], [GL_City_ID], [GL_Share], [GL_PartnerName], [GL_ActualizationDate], [AccessMap], [CommChampion_id], [Director_ID], [GL_Fax], [GL_MFGProCode], [GL_State], [GL_Street], [GL_Tel], [GL_ZIP], [ToolsChampion_id]) VALUES (N'aaf913cd-830d-4be2-b0f6-485da36aa663', N'Madrid', N'3TC', N'89cbac5f-4cca-4913-b770-38eeaa67142c', 100, NULL, '20071219 17:24:08.000', NULL, NULL, N'8a1552a9-3e4c-4c37-92d9-e80f84d16995', NULL, N'ES70', NULL, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Inn_GemaltoLocations]([GL_ID], [GL_Name], [GL_Code], [GL_City_ID], [GL_Share], [GL_PartnerName], [GL_ActualizationDate], [AccessMap], [CommChampion_id], [Director_ID], [GL_Fax], [GL_MFGProCode], [GL_State], [GL_Street], [GL_Tel], [GL_ZIP], [ToolsChampion_id]) VALUES (N'c78a64d2-121b-402e-aa6b-bae9de85f42a', N'Toronto', N'TOR', N'b018c2b5-547c-4def-aaba-067e32d6f3e7', 100, NULL, '20080514 15:07:22.000', NULL, NULL, N'63368d30-9b88-42d7-b427-1bd6c474e048', NULL, N'CA01', NULL, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Inn_GemaltoLocations]([GL_ID], [GL_Name], [GL_Code], [GL_City_ID], [GL_Share], [GL_PartnerName], [GL_ActualizationDate], [AccessMap], [CommChampion_id], [Director_ID], [GL_Fax], [GL_MFGProCode], [GL_State], [GL_Street], [GL_Tel], [GL_ZIP], [ToolsChampion_id]) VALUES (N'cbb6eaad-7ee9-4dbe-b495-a44a3d54eb2d', N'Cuernavaca', N'CVA', N'd4cc1532-60dc-4c82-a33f-bbcbaed552a0', 100, NULL, '20080514 14:52:29.000', NULL, NULL, N'fgdgf', NULL, N'MX70', NULL, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Inn_GemaltoLocations]([GL_ID], [GL_Name], [GL_Code], [GL_City_ID], [GL_Share], [GL_PartnerName], [GL_ActualizationDate], [AccessMap], [CommChampion_id], [Director_ID], [GL_Fax], [GL_MFGProCode], [GL_State], [GL_Street], [GL_Tel], [GL_ZIP], [ToolsChampion_id]) VALUES (N'd65531d9-14a6-4752-a457-2faf3453fd2b', N'Johannesburg', N'JNB', N'4516a23b-c0f2-4b2a-893c-060c1d48e175', 100, NULL, '20080520 15:33:58.000', NULL, NULL, N'a28f5c36-08a7-43f1-856c-32a03ec3b96f', NULL, N'ZA70', NULL, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Inn_GemaltoLocations]([GL_ID], [GL_Name], [GL_Code], [GL_City_ID], [GL_Share], [GL_PartnerName], [GL_ActualizationDate], [AccessMap], [CommChampion_id], [Director_ID], [GL_Fax], [GL_MFGProCode], [GL_State], [GL_Street], [GL_Tel], [GL_ZIP], [ToolsChampion_id]) VALUES (N'e1c8c62c-94b4-482e-83ff-6a13b83e8704', N'WOFE', N'WOFE', N'3a83f5e6-4a14-4f8e-b45f-894d52c4a463', 100, NULL, '20071219 17:29:34.000', NULL, NULL, N'17502b64-de42-472f-9152-8ad97860dccc', NULL, N'CN30', NULL, NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Inn_GemaltoLocations]([GL_ID], [GL_Name], [GL_Code], [GL_City_ID], [GL_Share], [GL_PartnerName], [GL_ActualizationDate], [AccessMap], [CommChampion_id], [Director_ID], [GL_Fax], [GL_MFGProCode], [GL_State], [GL_Street], [GL_Tel], [GL_ZIP], [ToolsChampion_id]) VALUES (N'fef0d68e-2b93-4a45-bfdd-840e4aafbecd', N'Sao Paulo', N'GBN', N'b61cb05c-35f7-4c14-a00d-e3a865539de4', 100, NULL, '20071219 17:16:08.000', NULL, NULL, N'85095260-94f6-4fec-8394-514c6648df5c', NULL, N'BR70', NULL, NULL, NULL, NULL, NULL)
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GOIF @@TRANCOUNT = 1
BEGIN
PRINT 'Data Update Of [dbo].[Inn_GemaltoLocations] Successfully Completed'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Data Update Of [dbo].[Inn_GemaltoLocations] Failed'
END
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GOIF @@TRANCOUNT = 1
ALTER TABLE [dbo].[Inn_GemaltoLocations] ENABLE TRIGGER [Inn_GemaltoLocations_delete]
GOIF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GOIF @@TRANCOUNT = 1
ALTER TABLE [dbo].[Inn_GemaltoLocations] ENABLE TRIGGER [Inn_GemaltoLocations_insert]
GOIF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
select p.*,n.GL_Name from Inn_GemaltoLocations n left join Inn_Industrial_PlantLayout p on n.GL_ID=p.GL_ID
就是Inn_Industrial_PlantLayout 没有对应的GL_ID
但是却能在页面上显示了Inn_GemaltoLocations对应的GL_ID的数据
是不是碰到枪枪你了?:)