Click here to Skip to main content
15,911,646 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Hi Freinds,
I've written a store procedure for Clerk table and i want when i run the store procedure one trigger happen. I said if the clk_Id when want to delete existence in another table give me a message but my code don't run.
Could you help me?

SQL
CREATE Procedure [dbo].[Clerk_Delete]

@clk_Id		int

As
Begin
    Begin Try
        Begin Tran a
            Delete From Clerk
            Where clk_Id = @clk_Id
            Commit Tran a;
    End Try
    Begin Catch
        Rollback Tran a;
    End Catch
End



SQL
CREATE Trigger [dbo].[Clerk_Insert_Trigger] on [dbo].[Clerk]
For delete
As
--declare @a int;
--select @a = Clerk.clk_Id from Clerk

If  exists (Select HokmeKargozini.clk_Id  from HokmeKargozini )
Begin
  print 'You don''t remove this clerk ';
  ROLLBACK tran a;
End
else
Begin
    print 'It''s removed ';
End
Posted
Updated 4-Aug-13 7:11am
v2
Comments
Maciej Los 4-Aug-13 14:34pm    
Not clear... ;(

1 solution

 
Share this answer
 
Comments
Elham.Deljooei 6-Aug-13 6:26am    
Hi Maciej Los,
I meant to say, if this clk_Id exists in another tables, don't remove it and do rollback.
'If exists (Select HokmeKargozini.clk_Id from HokmeKargozini )'
but it don't work.
Maciej Los 6-Aug-13 6:39am    
Have a look at example:
DECLARE @id INT
SELECT @id = COALESCE(clk_Id,0)
FROM HokmeKargozini)

IF (@id = 0)
PRINT 'Doesn''t exists'
Elham.Deljooei 6-Aug-13 6:45am    
Your mean, I write it in trigger?
How to do rollback? else clk_Id removed from table and it exists in another tables.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900