trigger problem inserting null values??

Question

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.

Answer 1

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

Answer 2

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/

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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.

Answer 7

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

Can you show the code so far you wrote??

Answer 8

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.

Answer 9

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.

Answer 10

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

Answer 11

 

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.

Answer 12

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

Answer 13

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

 

Answer 14

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')

Answer 15

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??

Answer 16

@ 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..........

 

 

Answer 17

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

Answer 18

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, [e-mail]
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

Answer 19

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

Answer 20

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

Answer 21

> 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.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
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