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.