Click here to Skip to main content
15,566,615 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

SQL
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
C#
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.
Posted

1 solution

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...
 
Share this answer
 
Comments
Prasad Bhujbal 3-Feb-14 15:29pm    
Here is my stored procedure
create procedure [dbo].[proc_email_sms_setting](@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))
as begin
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
Kornfeld Eliyahu Peter 3-Feb-14 15:34pm    
Yes. As I thought. You have to understand that the @smtpserver declared in the stored procedure IS NOT the same as the one in the trigger!!!
If you want to get the value just inserted you should do something like this (inside the trigger):
SELECT @SMTPSERVER = SMTPSERVER FROM INSERTED
and after that you may use it for anything you like...
Prasad Bhujbal 3-Feb-14 15:40pm    
Sir, I want to store only one record in table. If record already exist update it otherwise insert it.
Kornfeld Eliyahu Peter 3-Feb-14 15:43pm    
So don't do the trigger thing - it useless
Read this thread - all the answers there...
http://www.codeproject.com/Forums/1725/Database.aspx?fid=1725&tid=4745678
Prasad Bhujbal 3-Feb-14 15:52pm    
Thank you..

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