How to Perform a Procedure at Every Insert?

Asked

Viewed 91 times

1

When I go to register I want according to the selected category to record a moderator id, in the question table.

select idcategoria from tb_pergunta
select idmoderador,idcategoria from tb_moderador


USE [BANCOTESTE]
GO
/****** Object:  StoredProcedure [dbo].[ATUALIZAMODERADOR]    Script Date: 06/15/2015 13:14:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ATUALIZAMODERADOR]
 @IDPERGUNTA INT 
AS
BEGIN
 DECLARE @IDCATEGORIA INT
 DECLARE @IDMODERADOR INT

 SELECT  @IDCATEGORIA = IDCATEGORIA FROM TB_PERGUNTA WHERE IDPERGUNTA = @IDPERGUNTA
 SELECT  @IDMODERADOR = IDMODERADOR FROM TB_MODERADOR WHERE IDCATEGORIA = @IDCATEGORIA
 UPDATE TB_PERGUNTA SET  IDMODERADOR = @IDMODERADOR, BLOQUEIO_PERGUNTA='N', EXCLUIR_PERGUNTA='N'  WHERE IDPERGUNTA = @IDPERGUNTA

END
sql sql-server-2012

  • Tie your previous to a Rigger on the table

  • @gmsantos Creation would look like this? has another way of doing? I made the change of the question code, thank you

  • It would be something like that. I won’t be able to check the bottom now but it seems that it is right yes.

  • @gmsantos - It worked, I made change the code is posted again, very grateful for your help!

  • edit your question, extracting the solution as an answer and leaving the initial question only as a question. You have no problem answering your own question :)

1 answer

0

The solution would be the creation of a triggers

Creation would look like this:

Insert into tb_pergunta values ('1','1','','title question','Description','','','')

USE [BANCOTESTE]
GO
/****** Object:  Trigger [dbo].[TGR_TB_PERGUNTA]    Script Date: 06/15/2015 15:11:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TGR_TB_PERGUNTA]
 ON [dbo].[TB_PERGUNTA]
 FOR INSERT
AS
BEGIN
   DECLARE @IDCATEGORIA INT
   DECLARE @IDMODERADOR INT

   SELECT  @IDCATEGORIA = IDCATEGORIA FROM INSERTED
   SELECT  @IDMODERADOR = IDMODERADOR FROM TB_MODERADOR WHERE IDCATEGORIA = @IDCATEGORIA
   UPDATE TB_PERGUNTA SET  IDMODERADOR = @IDMODERADOR, BLOQUEIO_PERGUNTA='N', EXCLUIR_PERGUNTA='N'  
END

Browser other questions tagged sql sql-server-2012

You are not signed in. Login or sign up in order to post.