/* 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