Friday, April 17, 2009

restrict delete record which is used in other table in mssql

1. Create Scalar value Function in sql
ex:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



create FUNCTION [dbo].[GetApplicationDelete]
(
@ApplicationId numeric(18,0)
)
RETURNS bit
AS
BEGIN
-- Declare the return variable here
DECLARE @flg as bit;
declare @count as numeric(18,0);

-- Add the T-SQL statements to compute the return value here
select @count = SUM(b) from
(
SELECT count(*) b from dbo.ApplicationApprover
where ApplicationId = @ApplicationId
union
select count(*) b from .dbo.Cases
where ApplicationId = @ApplicationId



) tablename

if @count = 0
set @flg =1;
else
set @flg =0;


-- Return the result of the function
RETURN @flg

END

2 .Use this function in Sql Query

Ex:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




create PROCEDURE [dbo].[APPLICATION_FindAll]
AS
SET NOCOUNT ON

SELECT
ApplicationId,
AppCode,
AppName,
Description,
Remarks,
AppType,
IsActive,
IsDelete,
AppStartdate,
AppEnddate,
CreateBy,
CreateDate,
UpdateBy,
UpdateDate,
[dbo].GetApplicationDelete(ApplicationId) delflg
FROM [APPLICATION]

GO

3. Apply in Form in Grid View Delete Button
<asp:TemplateField HeaderText="Delete">

<ItemTemplate>

<asp:ImageButton ID="imgbtnDelete" runat="server" AlternateText="Delete" CausesValidation="False"

CommandName="Delete" ImageUrl="~/App_Themes/Image/Delete.gif" OnClientClick="Are You Sure?"

Visible='<%# Eval("delflg") %>' />

</ItemTemplate>

<ItemStyle HorizontalAlign="Center" />

</asp:TemplateField>

No comments:

ShareThis

Welcome

Welcome to Rajesh Prajapati, asp.net blog.
Here you can find some useful code and information about asp.net., c#, VB.net, SQL Server, Web Service, Web Designing etc