Triggers Query

Question

CREATE TABLE tbl
    (
      eNbr INT NOT NULL
    , uNbr INT NULL
    , vDate DATE NOT NULL
    );

INSERT  INTO tbl
        ( eNbr , uNbr , vDate )
VALUES  ( 5585 , NULL , '2012-01-01' );

INSERT  INTO tbl
        ( eNbr , uNbr , vDate )
VALUES  ( 5585 , 1 , '2012-01-01' );

I have a table tbl as above. I want to create a trigger

As when the inserted row has a NULL in uNbr then it checks the table on eNbr and vDate if the record exists error is raised

 when the inserted row has a value in uNbr then it checks the table on eNbr, uNbr  and vDate if the record exists error is raised.

 

---

Regards, Muhammad Bilal.

Answer 1

Create Trigger tb1InsertTr On tbl After Insert As
Begin
  If Exists (Select t.eNbr, t.vDate
    From tbl t
    Inner Join inserted i On t.eNbr = i.eNbr And t.vDate = i.vDate And i.uNbr Is Null
    Group By t.eNbr, t.vDate
    Having Count(*) > 1)
    Begin
      RollBack;
      RaisError('Duplicate eNbr and vDate where uNbr is NULL', 16, 1);
    End;
  Else If Exists (Select t.eNbr, t.uNbr, t.vDate
    From tbl t
    Inner Join inserted i On t.eNbr = i.eNbr And t.vDate = i.vDate And t.uNbr = i.uNbr
    Group By t.eNbr, t.uNbr, t.vDate
    Having Count(*) > 1)
    Begin
      RollBack;
      RaisError('Duplicate eNbr, uNbr and vDate', 16, 1);
    End;
End
go

Tom

Answer 2

This is completely wrong. A table has to have a key

CREATE TABLE Foobar
(e_nbr INTEGER NOT NULL, 
 u_nbr INTEGER,
 v_date DATE NOT NULL,
 PRIMARY KEY (e_nbr, v_date));

>> have a table Foobar as above. I want to create a trigger << 

SQL programmers want to avoid them because they are procedural code. This is a declarative language 

>> As when the inserted row has a NULL in u_nbr then it checks the table on e_nbr and v_date if the record [sic]e xists error is raised. <<
That is what a key prevents

>> when the inserted row has a value in u_nbr then it checks the table on e_nbr, u_nbr and v_date if the record [sic] exists error is raised. <<

There is no sequence of operations in SQL. Think about it. If (e_nbr, v_date) is unique, then the entire row is unique.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL