trigger problem inserting null values??

Article
10/03/2010

Question

_{Sunday, October 3, 2010 10:39 AM}

I have created a trigger for insert and whenever i insert the data the trigger should fire and notify some value inserted i want to create log table to save this information,now i have created a trigger

USE [mine]
GO
/****** Object: Trigger [dbo].[insert2]  Script Date: 10/03/2010 15:00:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[insert2] 
  on [dbo].[emp] 
  after insert 
  as 
  declare @sno int,@result varchar(50),@empid int,@name varchar(50) 
select @empid=empid,@name=@name from emp  
 insert into triglog values(@empid,@name)

but when i insert data into emp im getting error like inserting null values into triglog but how to get confirmation that a record is inserted im so much confused with this help me with this,thanks.

All replies (21)

_{Sunday, October 3, 2010 10:50 AM ✅Answered | 1 vote}

create table emp(empid int, name varchar(20))
create table triglog(empid int, name varchar(20))

create trigger trig_emp_ins
on emp
after insert
as
begin
declare @empid int, @name varchar(20)
select @empid = empid, @name = name from inserted
insert into triglog values(@empid, @name)
end

insert into emp values(1,'Krystian')

select * from triglog

With kind regards
Krystian Zieja
http://www.projectnenvision.com
Follow me on twitter
My Blog

_{Sunday, October 3, 2010 10:55 AM}

Your and Krystian's examples do not hold muulptile values that could be inserted, what if your inserted table has more than one row, guess , what value will be assign to @empid , @name variables , right , the last one

Fix it by the below

INSERT INTO triglog (empid,name) SELECT empid,name FROM inserted

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

_{Sunday, October 3, 2010 1:40 PM}

Can you post the structure of the TrigLog table?

In addition, you may want to read this blog post

Best Practice: Coding SQL Server triggers for multi-row operations

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Sunday, October 3, 2010 4:18 PM}

hi i worked on your code but im getting the same error

Cannot insert the value NULL into column 'name', table 'mine.dbo.triglog'; column does not allow nulls. INSERT fails.
The statement has been terminated.

_{Sunday, October 3, 2010 4:20 PM}

USE [mine]
GO
/****** Object: Table [dbo].[triglog]    Script Date: 10/03/2010 21:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[triglog](
    [empid] [int] NOT NULL,
    [name] [varchar](50) NOT NULL,
CONSTRAINT [PK_triglog] PRIMARY KEY CLUSTERED
(
    [empid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

_{Sunday, October 3, 2010 4:21 PM}

hi i worked on your code but im getting the same error

Cannot insert the value NULL into column 'name', table 'mine.dbo.triglog'; column does not allow nulls. INSERT fails.
The statement has been terminated.

_{Sunday, October 3, 2010 4:36 PM}

It should work..... are you inserting "NULL" as Name into Emp table?????

Can you show the code so far you wrote??

_{Sunday, October 3, 2010 4:37 PM}

cmd = new SqlCommand("insert1", con);
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.Add(new SqlParameter("@empid", SqlDbType.Int)).Value = TextBox1.Text;
         cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar)).Value = TextBox2.Text;
         con.Open();
         SqlDataReader dr;
             SqlCommand cmd2=new SqlCommand("select * from emp where empid='"+TextBox1.Text+"'",con);
             dr=cmd2.ExecuteReader();
             if (dr.HasRows)
             {
                 if (dr.Read())
                 {
                     if (TextBox1.Text == dr[0].ToString())
                     {
                         Response.Write("id already exists");

}

                   }
             }
             else
             {
                 dr.Close();
                 cmd.ExecuteNonQuery();
                 Response.Write("values inserted");

             }

im inserting values through stored proc.

_{Sunday, October 3, 2010 4:39 PM}

im much confused in writing the trigger i mean i want to know the trigger is fired this is what i want to achieve, thats why i am inserting values into another table.

_{Sunday, October 3, 2010 4:42 PM}

I asked the stored procedure and trigger, after you change the code as per the advice of krystian.

By, the way, there is a big security mistake is there in your below line

" SqlCommand cmd2=new SqlCommand("select * from emp where empid='"+TextBox1.Text+"'",con);"

have a look at below URL.

http://en.wikipedia.org/wiki/SQL_injection

_{Sunday, October 3, 2010 4:49 PM}

hi it worked i think the problem is with begin i think when i change the code with begin and end it is getting into triglog table.I dont know why it dint worked before.

_{Sunday, October 3, 2010 4:54 PM}

but if i just want to enter "record inserted" into a table how to do this after the record is inserted.

_{Sunday, October 3, 2010 4:56 PM}

Its not Begin--end problem... it might be some other... One more advise is,we can write like below also... it has 2 advantages

1. In case of insertion of multiple rows at a time, below code will not break.where as your code will break.

2. It will not have additional overhead of variables.

create trigger triggername on tablename after insert 
as
insert into TableName 
select ID from inserted

_{Sunday, October 3, 2010 4:57 PM}

but if i just want to enter "record inserted" into a table how to do this after the record is inserted.

change trigger code like.

create trigger triggername on tablename after insert 
as
insert into TableName values ('record inserted')

_{Sunday, October 3, 2010 5:05 PM}

here i want to know select @empid = empid, @name = name from inserted

@empid is row in my emp table and empid is row in my triglog right??

_{Sunday, October 3, 2010 5:10 PM}

@ will indicates the Local variable. @empid is like the int a; in front-end

"Inserted" is a system related table, which consists of the rows inserted rows in the previous insert operation.

When you insert a row, "Inserted" will consists of the inserted record details empID and Name.

We are inserting that 2 values, into 2 local variables @empid and @Name

Then in next line, we are inserting that 2 variables in triglog table..........

_{Sunday, October 3, 2010 6:57 PM}

The trigger code should be

ALTER trigger [dbo].[EmployeeInsert] 
 on [dbo].[emp] 
 after insert 
 as 
  if @@ROWCOUNT = 0
    return
  insert into TrigLog
  (EmpID, [Name])
  select EmpID, [Name]
  from Inserted

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Sunday, October 3, 2010 8:15 PM}

cmd = new SqlCommand("insert1", con);
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.Add(new SqlParameter("@empid", SqlDbType.Int)).Value = TextBox1.Text;
         cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar)).Value = TextBox2.Text;
         con.Open();
         SqlDataReader dr;
             SqlCommand cmd2=new SqlCommand("select * from emp where empid='"+TextBox1.Text+"'",con);
             dr=cmd2.ExecuteReader();

A couple of flaws here. First as Ramireddy poined out, interleaving the input value into the query is string is very bad. Don't do it, use parameters instead.

Next, if you want to check whether the row exists it would be better to do:

 SqlCommand cmd2=new SqlCommand(
     "select CASE WHEN EXISTS (SELECT * FROM emp WHERE empid = @empid")
          cmd.Parameters.Add(new SqlParameter("@empid", SqlDbType.Int)).Value = TextBox1.Text;  
      emp_exists = cmd2.ExecuteScalar()
      if (emp_exists) {

But even better is to have all logic in one place, to wit the stored procedure - or the client code. Now you have a mixup, which is confusing.

As for the original question, the trigger should go as Uri posted:

CREATE TRIGGER triggerhappy ON emp AFTER INSERT, UPDATE AS
   INSERT INTO triglog (empid,name) SELECT empid,name FROM inserted

And absolutely no variables in it. (Because you must be able to handle multi-row inserts.)

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

_{Wednesday, October 6, 2010 10:02 AM}

Thanks for the reply,but i dont know how to use the parameters can you explain me in short.

_{Wednesday, October 6, 2010 1:36 PM}

As Erland already showed, SqlCommand has Parameters collection. The simplest way to turn

SqlCommand cmd2=new SqlCommand("select * from emp where empid='"+TextBox1.Text+"'",con);

into parameterized code will be

 SqlCommand cmd2=new SqlCommand("select * from emp where empid=@EmpID",con);

  cmd2.Parameters.AddWithValue("@EmpID",TextBox1.Text);

You may want to start to read from this reference Using ADO.NET Parameters and also Configuring ADO.NET Parameters

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Wednesday, October 6, 2010 9:59 PM}

> Thanks for the reply,but i dont know how to use the parameters can you explain me in short.

That's funny, because you used parameters when calling the stored procedure! Using parameters with ad-hoc SQL is no different.

Connect, code, and grow

Share via

Question

All replies (21)

Best Practice: Coding SQL Server triggers for multi-row operations

Share via

trigger problem inserting null values??

Question

All replies (21)

Best Practice: Coding SQL Server triggers for multi-row operations

Additional resources