"Database Developer: Cooking with Database Ingredients"
by Ken North
Web Techniques, April 1999

Web Techniques grants permission to use these listings (and code) for 
private or commercial use provided that credit to Web Techniques and 
the author is maintained within the comments of the source. For 
questions, contact editors@web-techniques.com. 


[LISTING ONE]


/*  Sunday, January 17, 1999 8:58:33 PM   */

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Article
     (
     Article int NOT NULL IDENTITY (1, 1),
     Title varchar(60) NOT NULL,
     PubDate datetime NOT NULL,
     AuthorID int NULL,
     Pubname varchar(40) NOT NULL,
     Abstract varchar(128) NULL
     ) ON [PRIMARY]
GO
ALTER TABLE dbo.Article ADD CONSTRAINT
     PK_Article PRIMARY KEY NONCLUSTERED 
     (
     Article
     ) ON [PRIMARY]
GO
COMMIT



[LISTING TWO]


/* Create tables for articles database   */

use articles  
go


BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
go

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
COMMIT


/* Create keyword table, add primary key and foreign key constraints */
BEGIN TRANSACTION

create table Keyword ( 
     Article int constraint Keyword_Article not null check (
          Article >= 1),
     Idxkey varchar(30) not null)  
go

alter table Keyword
     add constraint Keyword_PK primary key (Article, Idxkey)   
go

/* Add keyword-article link */
alter table Keyword
     add constraint Article_Keyword_FK1 foreign key (
          Article)
      references Article (
          Article)  
go

COMMIT


/* Create Author table and add primary key constraint */
BEGIN TRANSACTION
create table Author ( 
     AuthorID int not null identity (1, 1),
     Surname varchar(40) not null,
     FirstName varchar(30) not null,
     PhoName varchar(4) null)  
go

alter table Author
     add constraint Author_PK primary key (AuthorID)   
go

COMMIT

/* Create Locator table, add primary and foreign key constraints */
BEGIN TRANSACTION
create table Locator ( 
     Article int constraint Locator_Article not null check (Article >= 1),
     URL varchar(127) not null)  

go

alter table Locator
     add constraint Locator_PK primary key (Article, URL)   
go

alter table Locator
     add constraint Article_Locator_FK1 foreign key (
          Article)
      references Article (
          Article)  
go

COMMIT


/* Add foreign key constraint to Article */
alter table Article
     add constraint Author_Article_FK1 foreign key (
          AuthorID)
      references Author (
          AuthorID)  
go


/**** Create article UPDATE trigger ****/
Create Trigger Articleupdate on Article 
for update 
as
BEGIN
  declare
     @rowsAffected int,
     @nullRows int,
     @validRows int, 
     @errorNumber int,
     @errorMsg varchar(255)

  select @rowsAffected = @@rowcount

/* trigger for ON UPDATE to PARENT NO ACTION (RESTRICT) */
if
update(Article)
  begin
    if exists(
      select * from deleted, Keyword
      where Keyword.Article = deleted.Article)
    begin
      select @errorNumber = 30002,
             @errorMsg = 'Cannot modify Article values in Article table 
                          because of dependent values in Keyword table'
      goto errorHandler
    end
  end


/* trigger for ON UPDATE to PARENT NO ACTION (RESTRICT) */
if
update(Article)
  begin
    if exists(
      select * from deleted, Locator
      where Locator.Article = deleted.Article)
    begin
      select @errorNumber = 30002,
             @errorMsg = 'Cannot modify Article values in Article table 
                          because of dependent values in Locator table'
      goto errorHandler
    end
  end
  return

errorHandler:
  raiserror @errorNumber @errorMsg
  rollback transaction
END

go


/**** Create author UPDATE trigger ****/
Create Trigger Authorupdate on Author 
for update 
as
BEGIN
  declare
     @rowsAffected int,
     @nullRows int,
     @validRows int, 
     @errorNumber int,
     @errorMsg varchar(255)

  select @rowsAffected = @@rowcount

/* trigger for ON UPDATE to PARENT NO ACTION (RESTRICT) */

if
update(AuthorID)
  begin
    if exists(
      select * from deleted, Article
      where Article.AuthorID = deleted.AuthorID)
    begin
      select @errorNumber = 30002,
             @errorMsg = 'Cannot modify AuthorID values in Author table 
                          because of dependent values in Article table'
      goto errorHandler
    end
  end
  return

errorHandler:
  raiserror @errorNumber @errorMsg
  rollback transaction
END

go


/**** Create article DELETE trigger ****/
Create Trigger Articledelete on Article 
for delete 
as
BEGIN
  declare
     @errorNumber int,
     @errorMsg varchar(255)

/* trigger for ON DELETE to PARENT NO ACTION (RESTRICT) */

  if exists(
     select * from deleted, Keyword     where Keyword.Article = deleted.Article)
    begin
      select @errorNumber = 30004,
             @errorMsg = 'Cannot delete from Article because Keyword 
                          dependency exists.'
    goto errorHandler
  end

/* trigger for ON DELETE to PARENT NO ACTION (RESTRICT) */

  if exists(
     select * from deleted, Locator     where Locator.Article = deleted.Article)
    begin
      select @errorNumber = 30004,
             @errorMsg = 'Cannot delete from Article because Locator 
                          dependency exists.'
    goto errorHandler
  end

  return
errorHandler:
  raiserror @errorNumber @errorMsg
  rollback transaction
END

go


/**** Create author DELETE trigger ****/
Create Trigger Authordelete on Author 
for delete 
as
BEGIN
  declare
            @errorNumber int,
            @errorMsg varchar(255)

/* trigger for ON DELETE to PARENT NO ACTION (RESTRICT) */

  if exists(
     select * from deleted, Article where Article.AuthorID = deleted.AuthorID)
    begin
      select @errorNumber = 30004,
             @errorMsg = 'Cannot delete from Author because Article 
                          dependency exists.'
    goto errorHandler
  end

  return
errorHandler:
  raiserror @errorNumber @errorMsg
  rollback transaction
END

go



[LISTING THREE]


Create Procedure InsertAuthor
@authid         int,
@surname        varchar(40),
@firstname       varchar (40),
@phonetic       varchar (4)
As
   insert into Author 
      (AuthorID, Surname, FirstName, PhoName)
   values
      (@authid, @surname, @firstname, @phonetic)
   return