Insert or Update null if the value is zero using Trigger on table

Insert or Update null if the value is zero using Trigger on table

• MatthieuGD

  Hi,

  I think it will not work. On Insertion it will throw error (referential constraint error) .

  As in Dept table there is no dept with deptno=0, So I wanted to pass null in emp.

  But from application zero value is passed for deptno for Emp.

  We get the following error "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Emp_Dept". The conflict occurred in database "Wosyst", table "dbo.Dept", column 'Deptno'.".

  Please Help me out

  Regards,

  Srinvas Govada

  
  
  

• Scottcha

  I have a solution on sql server 2000, but I think there isn't many difference between 2000 and 2005.

  CREATE TRIGGER Insert_Emp ON Emp
  FOR INSERT
  AS

  declare @Deptno int
  declare @Empno int
  SELECT @Deptno=Deptno, @Empno = Empno
  FROM inserted

  if @Deptno == 0
  BEGIN
  UPDATE Emp
  SET Deptno = 'your wanting value' //add your wanting values
  WHERE Empno = @Empno
  END

  The Update_Emp trigger is also like this.

  
  
  

• Fiaz Sheikh

  I would question if you really want to do this here and not in the application, but you can pretty easily do this with an instead of trigger. Here is the inserted trigger (without any error handling or anything) that deals with translating bad parentId values to NULL (and a commented out version that only works on 0):

  create table parent
  (
  parentId int primary key,
  value varchar(10) unique
  )
  create table child
  (
  childId int primary key,
  value varchar(10) unique,
  parentId int references parent(parentId) --FK to parent table
  )
  go

  create trigger [child$insteadOfInsert]
  on child
  instead of insert
  as
  begin
  set nocount on
  insert into child(childId, value,parentId)
  --this will be null if the parentId is not valid
  --and will work for multiple rows
  
  --only transform 0 to null
  --select childId, value,
  --case when inserted.parentId = 0 then NULL else inserted.parentId end
  
  select childId, value, (select parent.parentId
  from parent
  where parent.parentId = inserted.parentId)
  from inserted

  end
  go

  insert into child (childId, value, parentid)
  select 1,'invalid',1

  select *
  from child

  childId value parentId
  ----------- ---------- -----------
  1 invalid NULL

  insert into parent(parentId, value)
  select 1,'first'

  insert into child (childId, value, parentid)
  select 2,'valid-1',1
  union all
  select 3,'invalid-0',0
  union all
  select 4,'valid-1.2',1
  union all
  select 5,'invalid-12',12

  select *
  from child

  childId value parentId
  ----------- ---------- -----------
  1 invalid NULL
  2 valid-1 1
  3 invalid-0 NULL
  4 valid-1.2 1
  5 invalid-12 NULL
  

  
  
  

• DanThMan

  In my opinion, As your want to use the FR features

  So some of our operation have to be restricted, and we have to lose some easy way .

  1.but there are also another way to implement the function: we just didnot use the Foreign Reference, but restricted in the application layer.

  2. you can also restrict the inserting operation datas. that's mean when you want to insert a Emp, your have to offer the DeptNo. That's my method in my recent work of datebase system.

  
  
  

• el dio

  Has default value of deptno column Allow NULL

  Does Insert statement writes the deptno column

  I think the deptno must be same :

  deptno bigint FOREIGN KEY REFERENCES Dept (Deptno) NULL DEFAULT NULL

  And the insert statement is like this

  insert into Emp (Empno, EmpName) Values (1, N'Name comes here')

  because the SQL server triggers fires after the DML statement executed, the trigger procedure can not manipulate the rows affected by statement before the check contraints evaluates, i think.

  
  
• Insert or Update null if the value is zero using Trigger on table