开发技巧：删除资料保存日志并检查数据是否能删除

删除资料存储过程 SQL Script:

ALTER PROCEDURE [dbo].[usp_DeleteCustomer]
@Key VARCHAR(50),--客户编码
@User VARCHAR(20)='' --当前用户
AS
BEGIN

/******************************************************
程序说明：删除客户资料
-------------------------------------------------------
SELECT * FROM dbo.dt_Customer
SELECT * FROM NN_System.dbo.sys_LogOperation

SELECT * FROM dbo.tb_QO
-------------------------------------------------------
usp_DeleteCustomer 'M005211','admin'
usp_DeleteCustomer 'M005811','maggie'
*******************************************************/

DECLARE @MSG NVARCHAR(200)

--检查数据拥有者是否当前用户
IF @User<>(SELECT ISNULL(CreatedBy,'') FROM dbo.dt_Customer WHERE CustomerCode=@Key)
BEGIN
SELECT @MSG='您不能删除别人的数据!';
RAISERROR (@MSG,16,1);
RETURN;
END;

--统计关键业务数据使用次数
DECLARE @Count INT
SELECT @Count=
(SELECT COUNT(1) FROM tb_QO WHERE CustomerCode=@Key)+
(SELECT COUNT(1) FROM tb_PI WHERE CustomerCode=@Key)+
(SELECT COUNT(1) FROM tb_IV WHERE CustomerCode=@Key)+
(SELECT COUNT(1) FROM tb_PM WHERE CustomerCode=@Key)

IF (@Count>0)
BEGIN
SELECT @MSG='您不能删除当前资料，有'+CAST(@Count AS VARCHAR)+'个业务功能在使用!';
RAISERROR (@MSG,16,1);
RETURN;
END;

--删除数据
DELETE dt_Customer WHERE CustomerCode=@Key

IF @@ROWCOUNT=1
BEGIN
--添加删除日志
INSERT INTO NN_System.dbo.sys_LogOperation(OperationType,OperationTime,Account,MSG)
SELECT 'Delete',GETDATE(),@User,'删除《客户》记录,PK:'+@Key
END;

END;

//来源:C/S框架网(www.csframework.com) QQ:23404761

日志表 SQL Script:

CREATE TABLE [dbo].[sys_LogOperation]
(
[isid] [int] NOT NULL IDENTITY(1, 1),
[OperationType] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL,
[OperationTime] [datetime] NULL,
[Account] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL,
[FormName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[FormCaption] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[MenuName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[MSG] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[sys_LogOperation] ADD CONSTRAINT [PK_sys_LogOperation] PRIMARY KEY CLUSTERED ([isid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_sys_LogOperation] ON [dbo].[sys_LogOperation] ([Account]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_sys_LogOperation_1] ON [dbo].[sys_LogOperation] ([OperationType]) ON [PRIMARY]
GO


//来源:C/S框架网(www.csframework.com) QQ:23404761