触发更新sql server中的现有记录 [英] Trigger to Update existing record in sql server

问题描述

我想在插入后创建一个触发器，它将检查表是否包含记录。如果记录存在，我想更新该记录，否则插入该记录。

 创建  TRIGGER  tgr_setting  ON  Email_SMS_Setting 
 AFTER  INSERT  
  AS  
 开始 
  DECLARE   @ RC   INT ， @ smtpserver   varchar （ 100 ）， @ email_id   varchar （ 150 ）， @ email_pass   varchar （ 100 ），
  @sms_uname   varchar （ 100 ）， @sms_pass   varchar （ 100 ）， @sms_sender   varchar （ 100 ）， @sms_temp   varchar （ 500 ）
  SET   @ RC  =  @@ ROWCOUNT  
  IF  @RC！= 0 
  UPDATE  EMAIL_SMS_SETTING  SET  SMTPSERVER = @ SMTPSERVER，EMAIL_ID = @ EMAIL_ID，EMAIL_PASS = @ EMAIL_PASS，
 SMS_UNAME = @ SMS_UNAME，SMS_PASS = @ SMS_PASS，SMS_SENDER = @ SMS_SENDER，SMS_TEMP = @ SMS_TEMP 
  ELSE  
 插入 进入 Email_SMS_Setting （smtpserver，email_id，email_pass，sms_uname，sms_pass，sms_sender，sms_temp）
  values （ @ smtpserver ， @ email_id ， @ email_pass ， @sms_uname ， @sms_pass ， @sms_sender ， @sms_temp ）
  end  

要插入记录I写了

 SqlCommand cmd_setting =  new  SqlCommand（  proc_email_sms_setting，con_setting）; 
 cmd_setting.CommandType = CommandType.StoredProcedure; 
 cmd_setting.Parameters.AddWithValue（  @ smtpserver，txt_smtp_server.Text）; 
 cmd_setting.Parameters.AddWithValue（  @ email_id，txt_email_id.Text）; 
 cmd_setting.Parameters.AddWithValue（  @ email_pass，txt_email_pass.Text）; 
 cmd_setting.Parameters.AddWithValue（  @sms_uname，txt_sms_uname.Text）; 
 cmd_setting.Parameters.AddWithValue（  @ sms_pass，txt_sms_pass.Text）; 
 cmd_setting.Parameters.AddWithValue（  @ sms_sender，txt_sms_sender.Text）; 
 cmd_setting.Parameters.AddWithValue（  @sms_temp，txt_sms_temp.Text）; 
 con_setting.Open（）; 
 cmd_setting.ExecuteNonQuery（）; 
 con_setting.Close（）; 

在数据库中插入记录后，所有值都为空。

如果存在记录，我将如何插入记录和更新。

解决方案

您声明 @smtpserver varchar（100）但永远不会为它设置任何价值！肯定它会为null！

我相信你已经错误地使用存储过程中的 @smtpserve ...

I want to create a trigger after insert which will check whether table contains a record or not. if record exist, I want to update that record otherwise insert that record.

CREATE TRIGGER tgr_setting ON Email_SMS_Setting
 AFTER INSERT
 AS
  Begin
    DECLARE @RC INT , @smtpserver varchar(100), @email_id varchar(150), @email_pass varchar(100),
    @sms_uname varchar(100), @sms_pass varchar(100), @sms_sender varchar(100), @sms_temp varchar(500)
    SET @RC = @@ROWCOUNT
    IF @RC!=0
        UPDATE EMAIL_SMS_SETTING SET SMTPSERVER=@SMTPSERVER, EMAIL_ID=@EMAIL_ID, EMAIL_PASS=@EMAIL_PASS,
        SMS_UNAME=@SMS_UNAME, SMS_PASS=@SMS_PASS, SMS_SENDER =@SMS_SENDER, SMS_TEMP=@SMS_TEMP
    ELSE
            insert into Email_SMS_Setting (smtpserver,email_id,email_pass,sms_uname,sms_pass,sms_sender,sms_temp)
            values (@smtpserver, @email_id, @email_pass, @sms_uname, @sms_pass, @sms_sender, @sms_temp)
  end

To insert record I wrote

SqlCommand cmd_setting = new SqlCommand("proc_email_sms_setting", con_setting);
     cmd_setting.CommandType = CommandType.StoredProcedure;
     cmd_setting.Parameters.AddWithValue("@smtpserver", txt_smtp_server.Text);
     cmd_setting.Parameters.AddWithValue("@email_id", txt_email_id.Text);
     cmd_setting.Parameters.AddWithValue("@email_pass", txt_email_pass.Text);
     cmd_setting.Parameters.AddWithValue("@sms_uname", txt_sms_uname.Text);
     cmd_setting.Parameters.AddWithValue("@sms_pass", txt_sms_pass.Text);
     cmd_setting.Parameters.AddWithValue("@sms_sender", txt_sms_sender.Text);
     cmd_setting.Parameters.AddWithValue("@sms_temp", txt_sms_temp.Text);
     con_setting.Open();
     cmd_setting.ExecuteNonQuery();
     con_setting.Close();

After inserting record in database all values are null.
How I will insert record and update if exist a record.

解决方案

You declare @smtpserver varchar(100) but never set any value to it! For sure it will be null!
I believe you have mistaken it with the @smtpserve you have on the stored procedure...

这篇关于触发更新sql server中的现有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！