------------------------------------------------------------------------------------
-- update catalogue tables
------------------------------------------------------------------------------------

-- name relations
update RelNameQualifier set RelNameQualifier='is junior homonym of' where RelNameQualifierId=2;
update RelNameQualifier set RelNameQualifier='has substitute name' where RelNameQualifierId=3;
update RelNameQualifier set RelNameQualifier='is nominal taxon of ' where RelNameQualifierId=6;
update RelNameQualifier set RelNameQualifier='is protected against' where RelNameQualifierId=13;
update RelNameQualifier set RelNameQualifier='is surpressed in favour of' where RelNameQualifierId=14;
update RelNameQualifier set RelNameQualifier='is treated as junior homonym of' where RelNameQualifierId=15;

-- nomenclatural status
--update NomStatus set NomStatus='is treated as junior homonym of' where NomStatusId=15;










------------------------------------------------------------------------------------
-- add columns AuthorTeamYear and BasAuthorTeamYear to Name table
------------------------------------------------------------------------------------
if exists (select * from syscolumns where id = object_id ('[dbo].[Name]') and name = 'AuthorTeamYear')
ALTER TABLE [dbo].[Name] DROP COLUMN [AuthorTeamYear]
GO

ALTER TABLE [dbo].[Name] ADD [AuthorTeamYear] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

if exists (select * from syscolumns where id = object_id ('[dbo].[Name]') and name = 'BasAuthorTeamYear')
ALTER TABLE [dbo].[Name] DROP COLUMN [BasAuthorTeamYear]
GO

ALTER TABLE [dbo].[Name] ADD [BasAuthorTeamYear] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL


------------------------------------------------------------------------------------
-- update stored procedure dbo.s_web_OneName to return AuthorTeamYear and BasAuthorTeamYear
------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_web_OneName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_web_OneName]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/*	dbo.s_web_OneName		*/
/*	Author: A. Güntsch, J. Li	*/
/*	Created: 10/2001		*/
/*	Last update: 11/2006		*/
/*	Updated by: P. Ciardelli	*/
/* Select entry from Name  table  	*/
CREATE PROCEDURE dbo.s_web_OneName  @NameId INT  AS
BEGIN
	SELECT Name.PreliminaryFlag, FullNameCache, RankId, Rank, RankFk,  FullNomRefCache, RefDetailId, NameCache, UnnamedNamePhrase, SupragenericName, Genus, GenusSubdivisionEpi, SpeciesEpi,  InfraSpeciesEpi,   HybridFormulaFlag, 
			MonomHybFlag, BinomHybFlag, TrinomHybFlag, CultivarName, CultivarGroupName,  AuthorTeamFk, BasAuthorTeamFk,  ExAuthorTeamFk, ExBasAuthorTeamFk,  NomRefDetailFk, NomRefFk, NameSourceRefFk, Name.Notes, RefDetail.PreliminaryFlag AS DetailPreliminaryFlag, AuthorTeamYear, BasAuthorTeamYear 
		FROM  Rank,  Name LEFT OUTER JOIN RefDetail
			ON NomRefDetailFk = RefDetailId AND NomRefFk = RefFk
		WHERE
			@NameId = NameId and
			(RankFk = RankId) 
	
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


------------------------------------------------------------------------------------
-- created stored procedure dbo.s_web_UpdateName_AuthorTeamYear to save AuthorTeamYear and BasAuthorTeamYear
------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_web_UpdateName_AuthorTeamYear]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_web_UpdateName_AuthorTeamYear]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*	dbo.s_web_UpdateName_AuthorTeamYear	*/
/*	Author: P. Ciardelli			*/
/*	Created: 10/2001			*/
/* Update a name's authoryear and basauthoryear fields */
CREATE PROCEDURE dbo.s_web_UpdateName_AuthorTeamYear
						@NameId INT,
						@AuthorTeamYear NVARCHAR(40)=NULL,
						@BasAuthorTeamYear NVARCHAR(40) = NULL
AS BEGIN

	DECLARE @OneNameId INT

	UPDATE Name
	SET	AuthorTeamYear = @AuthorTeamYear,
		BasAuthorTeamYear = @BasAuthorTeamYear
	WHERE NameId = @NameId

	/* update basauthor fields of valid name, in case this name is a basionym */
	DECLARE CursValidName CURSOR  FOR
		SELECT  NameId
		FROM  Name, RelName
		WHERE	(NameFk1 = @NameId)
			AND(RelNameQualifierFk  = 1)
			AND(NameFk2 = NameId)

	OPEN CursValidName
	FETCH FROM CursValidName INTO @OneNameId
	WHILE (@@FETCH_STATUS <> -1) BEGIN
	/* Update accepted name */
	UPDATE Name
	SET BasAuthorTeamYear =  @AuthorTeamYear
	WHERE NameId = @OneNameId

	FETCH NEXT FROM CursValidName INTO @OneNameId
	END
	CLOSE CursValidName

	DEALLOCATE CursValidName

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


------------------------------------------------------------------------------------
-- update stored procedure dbo.s_web_AddIncludedPTaxon to save AuthorTeamYear and BasAuthorTeamYear
------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_web_AddIncludedPTaxon]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_web_AddIncludedPTaxon]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*	dbo.s_web_AddIncludedPTaxon		*/
/*	Author: A. Güntsch, J. Li		*/
/*	Created: 5/2001			*/
/*	Updated: P. Ciardelli		*/
/*	Last update: 11/2006	*/
/* Insert an "included" potential taxon and its name for the given reference, Add relation
between the given and the new potential taxon. HigherNameId and RefId are pointing
to the already existing higher taxon for which an included taxon is to be created. The @Low
parameter is used to indicate where to look for a new name id 
PC MOD - Added fields for Author Years for zoological data
*/
CREATE PROCEDURE dbo.s_web_AddIncludedPTaxon	@HigherNameId INT, 
						@RefId INT,
						@RankFk INT,
						@NameString NVARCHAR(160)=NULL,
						@AuthorTeamString NVARCHAR(160)=NULL,
						@FullReference NVARCHAR(255)=NULL,
						@UnnamedNamePhrase NVARCHAR(160)=NULL,
						@SupraGenericName NVARCHAR(50) = NULL,
						@Genus  NVARCHAR(50) = NULL,
						@GenusSubdivisionEpi NVARCHAR(50)=NULL,
						@SpeciesEpi NVARCHAR(50) = NULL,
						@InfraSpeciesEpi NVARCHAR(50) = NULL,
						@AuthorTeamFk INT = NULL,
						@BasAuthorTeamFk INT = NULL,
						@ExAuthorTeamFk INT = NULL,
						@ExBasAuthorTeamFk INT = NULL,
						@HybridFormulaFlag BIT = 0,
						@MonomHybFlag BIT = 0,
						@BinomHybFlag BIT = 0,
						@TrinomHybFlag BIT = 0,
						@CultivarGroupName NVARCHAR(160) = NULL,
						@CultivarName NVARCHAR(160) = NULL,
						@Notes NVARCHAR(1000)= NULL,
						@RelNotes NVARCHAR(1000) = NULL,
						@UserName NVARCHAR(255),
						@AuthorTeamYear NVARCHAR(40) = NULL,
						@BasAuthorTeamYear NVARCHAR(40) = NULL
										
AS BEGIN    
	DECLARE @NewNameId INT
	DECLARE @NewRefId INT
	DECLARE @RefDetailId INT
	DECLARE @CreatedWho NVARCHAR(255)
	SET @CreatedWho = @UserName

	
     	/* Create a new NameId */
    	SET @NewNameId = dbo.f_web_NextFreeNameId(@RefId * 1)
     	/* Insert the name */     
  	INSERT INTO Name (NameId, RankFk,  UnnamedNamePhrase, SupragenericName, Genus,  GenusSubdivisionEpi, SpeciesEpi, InfraSpeciesEpi, AuthorTeamFk, BasAuthorTeamFk, ExAuthorTeamFk, ExBasAuthorTeamFk, HybridFormulaFlag, MonomHybFlag, BinomHybFlag, TrinomHybFlag, CultivarGroupName, CultivarName, /* NameSourceRefFk,*/ Created_When, Created_Who, Notes, AuthorTeamYear, BasAuthorTeamYear)
	VALUES (@NewNameId, @RankFk, @UnnamedNamePhrase, @SupraGenericName, @Genus,  @GenusSubdivisionEpi, @SpeciesEpi, @InfraSpeciesEpi, @AuthorTeamFk, @BasAuthorTeamFk, @ExAuthorTeamFk, @ExBasAuthorTeamFk, @HybridFormulaFlag, @MonomHybFlag, @BinomHybFlag, @TrinomHybFlag, @CultivarGroupName, @CultivarName, /*@NameSourceRefFk,*/ getdate(), @CreatedWho, @Notes, @AuthorTeamYear, @BasAuthorTeamYear)
	/* Update the cache fields in case of rapid entry */     
	IF IsNull(@NameString,'')<>''  BEGIN
		UPDATE Name
		SET NameCache = LTRIM(RTRIM(@NameString)), 
			FullNameCache = RTRIM(LTRIM(RTRIM(@NameString)) + ' ' + LTRIM(RTRIM(IsNull(@AuthorTeamString,'')))),
			PreliminaryFlag = 1
		 WHERE (NameId = @NewNameId)
	END 
	IF IsNull(@FullReference,'')<>''  BEGIN
		SET @NewRefId = dbo.f_web_NextFreeRefId(@RefId * 1)
		SET @RefDetailId  = dbo.f_web_NextFreeRefDetailId(@NewRefId * 1)

		INSERT INTO Reference (RefId, RefCache, NomRefCache, PreliminaryFlag, RefCategoryFk, Created_When, Created_Who)
		VALUES (@NewRefId, @FullReference, @FullReference, 1, 10, GetDate(), @CreatedWho)

		INSERT INTO RefDetail (RefDetailId, RefFk, FullRefCache, FullNomRefCache, PreliminaryFlag, Created_When, Created_Who)
		VALUES (@RefDetailId, @NewRefId, @FullReference, @FullReference, 1, GetDate(), @CreatedWho)
	
		UPDATE Name
		SET NomRefFk = @NewRefId, NomRefDetailFk = @RefDetailId
		 WHERE (NameId = @NewNameId)
	END 

	
     	/* Create a potential taxon */
    	INSERT INTO PTaxon (PTNameFk, PTRefFk, /* NamePhrase,*/StatusFk, DoubtfulFlag, Created_When, Created_Who, Notes)
		VALUES (@NewNameId, @RefId,/*@NamePhrase, */1,  'a', GetDate(), @CreatedWho, @Notes)
    	 /* Insert relation for taxonomic inclusion */
	INSERT INTO RelPTaxon (PTNameFk1, PTRefFk1, PTNameFk2, PTRefFk2, RelQualifierFk, RelRefFk, Created_When, Created_Who, Notes)
	VALUES (@NewNameId, @RefId, @HigherNameId, @RefId, 1, @RefId, GetDate(), @CreatedWho, @RelNotes)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


------------------------------------------------------------------------------------
-- update stored procedure dbo.s_web_AddHomBas to save AuthorTeamYear and update acc. name's BasAuthorTeamYear
------------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_web_AddHomBas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_web_AddHomBas]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO



/*	dbo.s_web_AddHomBas			*/
/*	Author: J. Li			*/
/*	Created: 2/2002			*/
/*	Last update: 11/2006	*/
/* 	Updated by: P. Ciardelli	*/
/* Insert a basionym or a homonym for the given name by 
a) inserting a new name  (with eventually unstuctured data including reference), 
b) adding an entry in the RelName table. */
/* Added record creation for earlier homonyms - pc */
/* PC MOD - save AuthorTeamYear and update acc. name's BasAuthorTeamYear for zoo. data */
CREATE PROCEDURE dbo.s_web_AddHomBas			
						@RelNameQualifierFk INT, 
						@AcceptedNameId INT, 
						@AcceptedRefId INT,
						@RankFk INT,
						@NameString NVARCHAR(160)=NULL,
						@AuthorTeamString NVARCHAR(160)=NULL,
						@FullReference NVARCHAR(255)=NULL,
						@UnnamedNamePhrase NVARCHAR(160)=NULL,
						@SupraGenericName NVARCHAR(50) = NULL,
						@Genus  NVARCHAR(50) = NULL,
						@GenusSubdivisionEpi NVARCHAR(50)=NULL,
						@SpeciesEpi NVARCHAR(50) = NULL,
						@InfraSpeciesEpi NVARCHAR(50) = NULL,
						@AuthorTeamFk INT = NULL,
						@ExAuthorTeamFk INT = NULL,
						@BasAuthorTeamFk INT = NULL,
						@ExBasAuthorTeamFk INT = NULL,
						@HybridFormulaFlag BIT = 0,
						@MonomHybFlag BIT = 0,
						@BinomHybFlag BIT = 0,
						@TrinomHybFlag BIT = 0,
						@CultivarGroupName NVARCHAR(160) = NULL,
						@CultivarName NVARCHAR(160) = NULL,
						@Notes NVARCHAR(1000) = NULL,
						@RelNotes NVARCHAR(1000) = NULL,
						@UserName NVARCHAR(255),
						@IsEarlierHomonym CHAR(1) = NULL,
						@AuthorTeamYear NVARCHAR(40) = NULL,
						@BasAuthorTeamYear NVARCHAR(40) = NULL
						
AS BEGIN    
	DECLARE @NewNameId INT
    	DECLARE @NewRelNameId INT
	DECLARE @NewRefId INT
	DECLARE @RefDetailId INT
	DECLARE @CreatedWho NVARCHAR(255)
	SET @CreatedWho = @UserName
	SET @NewRelNameId = dbo.f_share_NextFreeRelNameId(1)
	
	/* Create a new NameId */
	SET @NewNameId = dbo.f_web_NextFreeNameId(@AcceptedRefId * 1)
	/* Insert the name */     
	INSERT INTO Name (NameId, RankFk,  UnnamedNamePhrase, SupragenericName, Genus,  GenusSubdivisionEpi, SpeciesEpi, InfraSpeciesEpi, AuthorTeamFk, BasAuthorTeamFk, ExAuthorTeamFk, ExBasAuthorTeamFk, HybridFormulaFlag, MonomHybFlag, BinomHybFlag, TrinomHybFlag, CultivarGroupName, CultivarName,  Created_When, Created_Who, Notes, AuthorTeamYear, BasAuthorTeamYear)
	VALUES (@NewNameId, @RankFk, @UnnamedNamePhrase, @SupraGenericName, @Genus,  @GenusSubdivisionEpi, @SpeciesEpi, @InfraSpeciesEpi, @AuthorTeamFk, @BasAuthorTeamFk, @ExAuthorTeamFk, @ExBasAuthorTeamFk, @HybridFormulaFlag, @MonomHybFlag, @BinomHybFlag, @TrinomHybFlag, @CultivarGroupName, @CultivarName, getdate(), @CreatedWho, @Notes, @AuthorTeamYear, @BasAuthorTeamYear)
	/* Update the cache fields in case of rapid entry */     
	IF IsNull(@NameString,'')<>''  BEGIN
		UPDATE Name
		SET NameCache = LTRIM(RTRIM(@NameString)), 
			FullNameCache = RTRIM(LTRIM(RTRIM(@NameString)) + ' ' + LTRIM(RTRIM(IsNull(@AuthorTeamString,'')))),
			PreliminaryFlag = 1
		 WHERE (NameId = @NewNameId)
	END 
	IF IsNull(@FullReference,'')<>''  BEGIN
		SET @NewRefId = dbo.f_web_NextFreeRefId(@AcceptedRefId * 1)
		SET @RefDetailId  = dbo.f_web_NextFreeRefDetailId(@NewRefId * 1)

		INSERT INTO Reference (RefId, RefCache, NomRefCache, PreliminaryFlag, RefCategoryFk, Created_When, Created_Who)
		VALUES (@NewRefId, @FullReference, @FullReference, 1, 10, GetDate(), @CreatedWho)

		INSERT INTO RefDetail (RefDetailId, RefFk, FullRefCache, FullNomRefCache, PreliminaryFlag, Created_When, Created_Who)
		VALUES (@RefDetailId, @NewRefId, @FullReference, @FullReference, 1, GetDate(), @CreatedWho)
	
		UPDATE Name
		SET NomRefFk = @NewRefId, NomRefDetailFk = @RefDetailId
		 WHERE (NameId = @NewNameId)
	END 
	
	/* Create a relname */

	/* NameFk1 and NameFk2 reversed when new record "is earlier homonym of" */
	IF @IsEarlierHomonym IS NOT NULL
		BEGIN
		INSERT INTO RelName (RelNameId, NameFk1, NameFk2, RelNameQualifierFk,  Created_When, Created_Who, Notes)
		VALUES (@NewRelNameId, @AcceptedNameId, @NewNameId, @RelNameQualifierFk, GetDate(), @CreatedWho, @RelNotes)
	END
	ELSE
	BEGIN
		INSERT INTO RelName (RelNameId, NameFk1, NameFk2, RelNameQualifierFk,  Created_When, Created_Who, Notes)

		VALUES (@NewRelNameId, @NewNameId, @AcceptedNameId, @RelNameQualifierFk, GetDate(), @CreatedWho, @RelNotes)
	END
	
	IF (@RelNameQualifierFk = 1) BEGIN
		/* Update accepted name */
		UPDATE Name
		SET BasAuthorTeamFk =  @AuthorTeamFk, BasAuthorTeamYear = @AuthorTeamYear
		WHERE NameId = @AcceptedNameId
	END
	
	
		
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


------------------------------------------------------------------------------------
-- created stored procedure dbo.s_web_AddSyn to save AuthorTeamYear and BasAuthorTeamYear
------------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_web_AddSyn]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_web_AddSyn]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




/*	dbo.s_web_AddSyn				*/
/*	Author: A. Güntsch, J. Li			*/
/*	Created: 10/2001			*/
/*	Updated: P. Ciardelli		*/
/*	Last update: 11/2006	*/
/* Insert a synonym for the given potential taxon by 
a) inserting a new name (with eventually unstuctured data including reference), 
b) creating a potential taxon for this name and 
c) adding an entry in the RelPTaxon table .
This procedure is similiar to dbo.s_web_AddIncludedTaxon 
in this database. */		
/* PC MOD - Added fields for Author Years for zoological data */		
						
CREATE PROCEDURE dbo.s_web_AddSyn			
						
						@QualifierFk INT,
						@AcceptedNameId INT, 
						@RefId INT,
						@RankFk INT,
						@NameString NVARCHAR(160)=NULL,
						@AuthorTeamString NVARCHAR(160)=NULL,
						@FullReference NVARCHAR(255)=NULL,
						@NamePhrase NVARCHAR(160)=NULL,
						@UnnamedNamePhrase NVARCHAR(160)=NULL,
						@SupraGenericName NVARCHAR(50) = NULL,
						@Genus  NVARCHAR(50) = NULL,
						@GenusSubdivisionEpi NVARCHAR(50)=NULL,
						@SpeciesEpi NVARCHAR(50) = NULL,
						@InfraSpeciesEpi NVARCHAR(50) = NULL,
						@AuthorTeamFk INT = NULL,
						@BasAuthorTeamFk INT = NULL,
						@ExAuthorTeamFk INT = NULL,
						@ExBasAuthorTeamFk INT = NULL,
						@HybridFormulaFlag BIT = 0,
						@MonomHybFlag BIT = 0,
						@BinomHybFlag BIT = 0,
						@TrinomHybFlag BIT = 0,
						@DoubtfulFlag  NVARCHAR(1) = 'a',
						@CultivarGroupName NVARCHAR(160) = NULL,
						@CultivarName NVARCHAR(160) = NULL,
						@Notes NVARCHAR(1000)= NULL,
						@RelNotes NVARCHAR(1000)= NULL,
						@UserName NVARCHAR(255),
						@AuthorTeamYear NVARCHAR(40)= NULL,
						@BasAuthorTeamYear NVARCHAR(40)= NULL
						
AS BEGIN    
	DECLARE @RelQualifierFk INT
	DECLARE @NewNameId INT
	DECLARE @NewRefId INT
	DECLARE @RefDetailId INT
	DECLARE @StatusFk INT
	DECLARE @CreatedWho NVARCHAR(255)
	
	IF @QualifierFk = 1 BEGIN
		SET @StatusFk = 1
		SET @RelQualifierFk = 1
	END ELSE IF @QualifierFk = 2 BEGIN
		SET @StatusFk = 2
		SET @RelQualifierFk = 2
	END ELSE IF @QualifierFk = 3 BEGIN
		SET @StatusFk = 3
		SET @RelQualifierFk = 5
	END ELSE IF @QualifierFk = 4 BEGIN
		SET @StatusFk = 4
		SET @RelQualifierFk = 4
	END ELSE IF @QualifierFk = 5 BEGIN
		SET @StatusFk = 2
		SET @RelQualifierFk = 7
	END ELSE IF @QualifierFk = 6 BEGIN
		SET @StatusFk = 3
		SET @RelQualifierFk = 103
	END ELSE IF @QualifierFk = 7 BEGIN
		SET @StatusFk = 4
		SET @RelQualifierFk = 101
	END ELSE IF @QualifierFk = 8 BEGIN
		SET @StatusFk = 2
		SET @RelQualifierFk = 6
	END ELSE IF @QualifierFk = 9 BEGIN
		SET @StatusFk = 3
		SET @RelQualifierFk = 104
	END ELSE IF @QualifierFk = 10 BEGIN
		SET @StatusFk = 4
		SET @RelQualifierFk = 102
	END

	SET @CreatedWho = @UserName 

	/* Create a new NameId */
	SET @NewNameId = dbo.f_web_NextFreeNameId(@RefId * 1)
	/* Insert the name */     
	INSERT INTO Name (NameId, RankFk,  UnnamedNamePhrase, SupragenericName, Genus,  GenusSubdivisionEpi, SpeciesEpi, InfraSpeciesEpi, AuthorTeamFk, BasAuthorTeamFk, ExAuthorTeamFk, ExBasAuthorTeamFk, HybridFormulaFlag, MonomHybFlag, BinomHybFlag, TrinomHybFlag, CultivarGroupName, CultivarName, /* NameSourceRefFk,*/ Created_When, Created_Who, Notes, AuthorTeamYear, BasAuthorTeamYear)
		VALUES (@NewNameId, @RankFk, @UnnamedNamePhrase, @SupraGenericName, @Genus,  @GenusSubdivisionEpi, @SpeciesEpi, @InfraSpeciesEpi, @AuthorTeamFk, @BasAuthorTeamFk, @ExAuthorTeamFk, @ExBasAuthorTeamFk, @HybridFormulaFlag, @MonomHybFlag, @BinomHybFlag, @TrinomHybFlag, @CultivarGroupName, @CultivarName,/* @NameSourceRefFk, */getdate(), @CreatedWho, @Notes, @AuthorTeamYear, @BasAuthorTeamYear)
	/* Update the cache fields in case of rapid entry */     
	IF IsNull(@NameString,'')<>''  BEGIN
		UPDATE Name
		SET NameCache = LTRIM(RTRIM(@NameString)), 
			FullNameCache = RTRIM(LTRIM(RTRIM(@NameString)) + ' ' + LTRIM(RTRIM(IsNull(@AuthorTeamString,'')))),
			PreliminaryFlag = 1
		 WHERE (NameId = @NewNameId)
	END 
	IF IsNull(@FullReference,'')<>''  BEGIN
		SET @NewRefId = dbo.f_web_NextFreeRefId(@RefId * 1)
		SET @RefDetailId  = dbo.f_web_NextFreeRefDetailId(@NewRefId * 1)

		INSERT INTO Reference (RefId, RefCache, NomRefCache, PreliminaryFlag, RefCategoryFk, Created_When, Created_Who)
		VALUES (@NewRefId, @FullReference, @FullReference, 1, 10, GetDate(), @CreatedWho)

		INSERT INTO RefDetail (RefDetailId, RefFk, FullRefCache, FullNomRefCache, PreliminaryFlag, Created_When, Created_Who)
		VALUES (@RefDetailId, @NewRefId, @FullReference, @FullReference, 1, GetDate(), @CreatedWho)
	
		UPDATE Name
		SET NomRefFk = @NewRefId, NomRefDetailFk = @RefDetailId
		 WHERE (NameId = @NewNameId)
	END 

	
	/* Create a potential taxon */
	
		INSERT INTO PTaxon (PTNameFk, PTRefFk,  NamePhrase, StatusFk, DoubtfulFlag, Created_When, Created_Who, Notes)
		VALUES (@NewNameId, @RefId, @NamePhrase, @StatusFk, @DoubtfulFlag, GetDate(), @CreatedWho, @Notes)
	
	/* Insert relation for synonymy  */
	   INSERT INTO RelPTaxon (PTNameFk1, PTRefFk1, PTNameFk2, PTRefFk2, RelQualifierFk, RelRefFk, Created_When, Created_Who, Notes )
	   VALUES (@NewNameId, @RefId, @AcceptedNameId, @RefId, @RelQualifierFk, @RefId, GetDate(), @CreatedWho, @RelNotes)
	
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO











------------------------------------------------------------------------------------
-- create new reference trigger to update years in name table
------------------------------------------------------------------------------------



SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/*
=============================================
	Reference ZooName Trigger
=============================================
	Author: Markus Döring
	Created: 11/2006
	Updated: 
=============================================

The ZooNamePubYear trigger will update zoological names that use this
reference as their or their basionyms nomenclatural reference.
The AuthorTeamYear and BasAuthorTeamYear field in table name is updated
with RefYear of this reference.
*/

CREATE TRIGGER ZooNamePubYear ON [dbo].[Reference] 
FOR INSERT, UPDATE AS
begin
	DECLARE @RefId INT
	/* retrieve RefId that was modified */
	SELECT @RefId = RefId FROM Inserted

	IF @RefId > 0 BEGIN
		DECLARE @prelim BIT
		DECLARE @RefYear nvarchar (40)
		SELECT @prelim = PreliminaryFlag, @RefYear = RefYear  FROM Reference WHERE RefId = @RefId
		-- only modify name record if there is any year data available
		if dbo.f_Core_replNullInt(@prelim) <> 1 begin
			-- no preliminary reference. use year even if its empty!
			update name set AuthorTeamYear=@RefYear where NomRefFk=@RefId
			-- check if basionyms exist
			update name set BasAuthorTeamYear=@RefYear where nameid in (
					select namefk2 from name n1 join relname on n1.nameid=namefk1 
					where RelNameQualifierfk=1 and n1.nomreffk=@RefId)
		end
	END
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



















------------------------------------------------------------------------------------
-- update names trigger
------------------------------------------------------------------------------------



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/*	CreateName			*/
/*	J.Li		     		*/
/*	Created: 6/2002			*/
/*	Updated: M.Döring		*/
/*	Last update: 10/2006		*/
/*
ALTER   the namecache AND fullnamecache WITH AND without author string WHENEVER one OF the fields IN a NAME TABLE
entry has been modified. The NAME strings ARE constructed according TO Euro+Med rules. Currently,
the following ranks ARE implemented: ANY suprageneric rank, genus, infrageneric ranks (subgenus, SECTION,
subsection, series, subseries,), aggregates, speciesgroup, species, infraspecific ranks (subspecies, convarietas,
variety, subvariety,  forma, subforma AND forma spec.). Make sure that this trigger IS modified AS soon AS new ranks
 appear OR the rules FOR builfing NAMES ARE changed.
 
 Modified to produce a zoological name incl year of nom.reference in fullnamecache
*/
ALTER   TRIGGER CreateName
ON dbo.Name
FOR INSERT, UPDATE AS
BEGIN
	DECLARE @zooCode BIT
	select top 1 @zooCode = ZooFlag from metadata
	
	DECLARE @prelim BIT
	DECLARE @NameId INT
	SELECT @NameId = NameId FROM Inserted
	SELECT @prelim = PreliminaryFlag
	FROM NAME
	WHERE NameId = @NameId
	IF dbo.f_Core_replNullInt(@prelim) <> 1 BEGIN
		/* Declarations */
		DECLARE @RankFk INT
		DECLARE @NameCache NVARCHAR(160)
		DECLARE @SupraGenericName NVARCHAR(50)
		DECLARE @Genus NVARCHAR(50)
		DECLARE @GenusSubdivisionEpi NVARCHAR(50)
		DECLARE @SpeciesEpi NVARCHAR(50)
		DECLARE @InfraSpeciesEpi NVARCHAR(50)
		DECLARE @InfraSpeciesEpiTemp NVARCHAR(50)
		DECLARE @AuthorTeamFk INT
		DECLARE @ExAuthorTeamFk INT
		DECLARE @BasAuthorTeamFk INT
		DECLARE @ExBasAuthorTeamFk INT

		DECLARE @MonomHybFlag BIT
		DECLARE @BinomHybFlag BIT
		DECLARE @TrinomHybFlag BIT

		DECLARE @FullNameCache NVARCHAR(255)
		DECLARE @UnnamedNamePhrase NVARCHAR(160)
  		DECLARE @AuthorStr NVARCHAR(240)
		DECLARE @AuthorTeam NVARCHAR(70)
		DECLARE @ExAuthorTeam NVARCHAR(50)
		DECLARE @BasAuthorTeam NVARCHAR(70)
		DECLARE @ExBasAuthorTeam NVARCHAR(50)
		DECLARE @CultivarGroupName NVARCHAR(50)
		DECLARE @CultivarName NVARCHAR(50)
		DECLARE @CultivarGroupAndCultivar NVARCHAR(100)
		DECLARE @NameWithoutCultivar NVARCHAR(100)
		DECLARE @BasAuthorStr NVARCHAR(240)
		DECLARE @NormalAuthorStr NVARCHAR(240)
		DECLARE @SupraGenAndGen NVARCHAR(100)

  		DECLARE @AuthorTeamYear NVARCHAR(50)
  		DECLARE @BasAuthorTeamYear NVARCHAR(50)

		DECLARE @HybridFormulaFlag BIT
		DECLARE @ChildrenNumber INT
		DECLARE @ChildrenNameId INT
		DECLARE @OldChildrenNameCache NVARCHAR(255)
		DECLARE @OldParentNameCache NVARCHAR(255)
		DECLARE @OldChildrenFullNameCache NVARCHAR(255)
		DECLARE @OldParentFullNameCache NVARCHAR(255)

		/* retrieve data that was modified */

		SELECT @RankFk = RankFk,
			@OldParentNameCache = NameCache,
			@OldParentFullNameCache = NameCache,
			@UnnamedNamePhrase = UnnamedNamePhrase,
			@SupraGenericName = SupragenericName,
			@Genus = Genus,
			@GenusSubdivisionEpi = GenusSubdivisionEpi,
			@SpeciesEpi = SpeciesEpi,
			@InfraSpeciesEpi = InfraSpeciesEpi,
			@AuthorTeamFk = AuthorTeamFk,
			@ExAuthorTeamFk = ExAuthorTeamFk,
			@BasAuthorTeamFk = BasAuthorTeamFk,
			@ExBasAuthorTeamFk = ExBasAuthorTeamFk,
			@AuthorTeamYear = AuthorTeamYear,
			@BasAuthorTeamYear = BasAuthorTeamYear,
			@HybridFormulaFlag = HybridFormulaFlag,
			@MonomHybFlag= MonomHybFlag,
			@BinomHybFlag = BinomHybFlag,
			@TrinomHybFlag = TrinomHybFlag,
			@CultivarGroupName = CultivarGroupName,
			@CultivarName = CultivarName
		FROM NAME
		WHERE NameId = @NameId

		/* retrieve AuthorTeamString */
		SELECT @AuthorTeam = dbo.f_core_replNullTrim(AuthorTeamCache)
		FROM   AuthorTeam
		WHERE  AuthorTeamId = @AuthorTeamFk
		/* retrieve Basionym AuthorTeamString */
		SELECT @BasAuthorTeam = dbo.f_core_replNullTrim(AuthorTeamCache)
		FROM   AuthorTeam
		WHERE  AuthorTeamId = @BasAuthorTeamFk
		/* retrieve ExAuthorTeamString */
		SELECT @ExAuthorTeam = dbo.f_core_replNullTrim(AuthorTeamCache)
		FROM   AuthorTeam
		WHERE  AuthorTeamId = @ExAuthorTeamFk
		/* retrieve ExBasAuthorTeamString */
		SELECT @ExBasAuthorTeam = dbo.f_core_replNullTrim(AuthorTeamCache)
		FROM   AuthorTeam
		WHERE  AuthorTeamId = @ExBasAuthorTeamFk

		/* The Author section */
		/* create substring for authors */
		if (@zooCode = 1) begin
			/* zological code */
			IF (@AuthorTeamYear <> '') and (@AuthorTeam <> '') BEGIN
				SET @NormalAuthorStr = @AuthorTeam + ', ' + @AuthorTeamYear
			END ELSE IF (@AuthorTeamYear <> '') BEGIN
				SET @NormalAuthorStr = @AuthorTeamYear
			END ELSE BEGIN
				SET @NormalAuthorStr = @AuthorTeam
			END
			SET @AuthorStr = @NormalAuthorStr
			-- add optional basionym?
			IF (@BasAuthorTeamYear <> '') and (@BasAuthorTeam <> '') BEGIN
				SET @AuthorStr = dbo.f_Core_StrSpaceAdd('(' + @BasAuthorTeam + ', ' + @BasAuthorTeamYear + ')', @NormalAuthorStr )
			END ELSE IF (@BasAuthorTeamYear <> '') BEGIN
				SET @AuthorStr = dbo.f_Core_StrSpaceAdd('(' + @BasAuthorTeamYear + ')', @NormalAuthorStr )
			END ELSE IF (@BasAuthorTeam <> '') BEGIN
				SET @AuthorStr = dbo.f_Core_StrSpaceAdd('(' + @BasAuthorTeam + ')', @NormalAuthorStr )
			END
		end else begin
			/* botanical code */
			IF (@ExBasAuthorTeam <> '')BEGIN
				SET @BasAuthorStr = dbo.f_Core_StrSpaceAdd(@ExBasAuthorTeam + ' ex', @BasAuthorTeam)
			END ELSE BEGIN
				SET @BasAuthorStr = @BasAuthorTeam
			END

			IF (@ExAuthorTeam <> '')BEGIN
				SET @NormalAuthorStr  = dbo.f_Core_StrSpaceAdd(@ExAuthorTeam + ' ex', @AuthorTeam)
			END ELSE BEGIN
				SET @NormalAuthorStr = @AuthorTeam
			END
			SET @AuthorStr = dbo.f_Core_StrSpaceAdd('(' + @BasAuthorStr + ')', @NormalAuthorStr  )
		end
		/* End of the Author section */
		
		IF (@HybridFormulaFlag <> 1 ) BEGIN
		/* The hybrid section */
			IF ((@MonomHybFlag = 1)OR (@BinomHybFlag = 1) OR (@TrinomHybFlag = 1))BEGIN
				IF (@MonomHybFlag = 1) AND NOT(@Genus LIKE '% x %') BEGIN /* named hybrid */
					SET @Genus = 'x ' + @Genus
				END
				/* BinomHybFlag = 1*/
				IF (@BinomHybFlag = 1) AND(@RankFk= 42) AND NOT(@GenusSubdivisionEpi LIKE '% x %') BEGIN/* infrageneric --> subgenus */
					SET @GenusSubdivisionEpi = 'nothosubgen. ' + @GenusSubdivisionEpi
				END
				IF (@BinomHybFlag = 1) AND(@RankFk >= 60 OR @RankFk= 58 OR @RankFk= 59) AND NOT(@SpeciesEpi LIKE '% x %') BEGIN
					SET @SpeciesEpi = 'x ' + @SpeciesEpi
				END

				IF (@BinomHybFlag = 1) AND(@RankFk= 45) AND NOT(@GenusSubdivisionEpi LIKE '% x %') BEGIN /* infrageneric --> section */

					SET @GenusSubdivisionEpi = 'nothosect. ' + @GenusSubdivisionEpi
				END

				IF (@BinomHybFlag = 1) AND(@RankFk= 47) AND NOT(@GenusSubdivisionEpi LIKE '% x %') BEGIN /* infrageneric --> section */

					SET @GenusSubdivisionEpi = 'nothosubsect. ' + @GenusSubdivisionEpi
				END

				IF (@BinomHybFlag = 1) AND(@RankFk= 50) AND NOT(@GenusSubdivisionEpi LIKE '% x %') BEGIN /* infrageneric --> section */

					SET @GenusSubdivisionEpi = 'nothoser. ' + @GenusSubdivisionEpi
				END

				IF (@BinomHybFlag = 1) AND(@RankFk= 52) AND NOT(@GenusSubdivisionEpi LIKE '% x %') BEGIN /* infrageneric --> section */

					SET @GenusSubdivisionEpi = 'nothosubser. ' + @GenusSubdivisionEpi
				END
				/*TrinomHybFlag = 1*/

				IF (@TrinomHybFlag = 1) AND(@RankFk= 65)AND NOT(@InfraSpeciesEpi LIKE '% x %') BEGIN /* infrspecies --> section */
					SET @InfraSpeciesEpi = 'nothosubsp. ' + @InfraSpeciesEpi
				END
				IF (@TrinomHybFlag = 1) AND(@RankFk= 68)AND NOT(@InfraSpeciesEpi LIKE '% x %') BEGIN /* infrspecies --> section */
					SET @InfraSpeciesEpi = 'nothoconvar. ' + @InfraSpeciesEpi
				END
				IF (@TrinomHybFlag = 1) AND(@RankFk= 70)AND NOT(@InfraSpeciesEpi LIKE '% x %') BEGIN /* infrspecies --> section */
					SET @InfraSpeciesEpi = 'nothovar. ' + @InfraSpeciesEpi
				END
				IF (@TrinomHybFlag = 1) AND(@RankFk= 73)AND NOT(@InfraSpeciesEpi LIKE '% x %') BEGIN /* infrspecies --> section */
					SET @InfraSpeciesEpi = 'nothosubvar. ' + @InfraSpeciesEpi
				END
				IF (@TrinomHybFlag = 1) AND(@RankFk= 80)AND NOT(@InfraSpeciesEpi LIKE '% x %') BEGIN /* infrspecies --> section */
					SET @InfraSpeciesEpi = 'nothof. ' + @InfraSpeciesEpi
				END
				IF (@TrinomHybFlag = 1) AND(@RankFk= 82)AND NOT(@InfraSpeciesEpi LIKE '% x %') BEGIN /* infrspecies --> section */
					SET @InfraSpeciesEpi = 'nothosubf. ' + @InfraSpeciesEpi
				END
				IF (@TrinomHybFlag = 1) AND(@RankFk= 84)AND NOT(@InfraSpeciesEpi LIKE '% x %') BEGIN /* infrspecies --> section */
					SET @InfraSpeciesEpi = 'nothof.spec. ' + @InfraSpeciesEpi
				END
				SET @NameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@SupraGenericName,@Genus), @GenusSubdivisionEpi), dbo.f_Core_StrSpaceAdd(@SpeciesEpi, @InfraSpeciesEpi))
				SET @FullNameCache = dbo.f_Core_StrSpaceAdd(@NameCache, @AuthorStr)

				IF (@CultivarName = '') AND (@CultivarGroupName = '' )AND(@UnnamedNamePhrase IS NULL) BEGIN
					SET @NameCache = @NameCache
					SET @FullNameCache = @FullNameCache
				END
				/*UnamedNamePhrase*/
				IF (@UnnamedNamePhrase IS NOT NULL ) BEGIN
					SET @NameCache = dbo.f_Core_StrSpaceAdd(@NameCache, '"' + @UnnamedNamePhrase + '"')
					SET @FullNameCache = dbo.f_Core_StrSpaceAdd(@FullNameCache, '"' + @UnnamedNamePhrase + '"')
				END

				/* cultivarname and cultivargroupname*/

				IF (@CultivarName IS NOT NULL) AND (@CultivarGroupName IS NOT NULL) BEGIN

					SET @CultivarGroupAndCultivar = dbo.f_Core_StrSpaceAdd('[' + @CultivarGroupName + ' Group]',   CHAR(0x27) + @CultivarName + CHAR(0x27))
					SET @NameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi),@CultivarGroupAndCultivar)
					SET @FullNameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi),@CultivarGroupAndCultivar)
				END ELSE

				IF (@CultivarName IS NOT NULL) AND (@CultivarGroupName IS NULL) BEGIN

					SET @NameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi), CHAR(0x27) + @CultivarName + CHAR(0x27))
					SET @FullNameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi), CHAR(0x27) + @CultivarName + CHAR(0x27))
				END ELSE
				IF (@CultivarName IS  NULL) AND (@CultivarGroupName IS NOT NULL) BEGIN
					SET @NameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi), @CultivarGroupName + ' Group')
					SET @FullNameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi), @CultivarGroupName + ' Group')
				END


			/* End of the hybrid section */
			END ELSE BEGIN
				/* The scientific Name Section */

				IF (@RankFk=42) BEGIN /* infrageneric --> subgenus */
					SET @GenusSubdivisionEpi = dbo.f_Core_StrSpaceAdd('subg.', @GenusSubdivisionEpi)
				END
				IF (@RankFk=45)  BEGIN /* infrageneric --> section */
					SET @GenusSubdivisionEpi = dbo.f_Core_StrSpaceAdd('sect.', @GenusSubdivisionEpi)
				END
				IF (@RankFk=47)  BEGIN /* infrageneric --> subsection */

					SET @GenusSubdivisionEpi = dbo.f_Core_StrSpaceAdd('subsect.', @GenusSubdivisionEpi)
				END
				IF (@RankFk=50)  BEGIN /* infrageneric --> series */

					SET @GenusSubdivisionEpi = dbo.f_Core_StrSpaceAdd('ser.', @GenusSubdivisionEpi)
				END
				IF (@RankFk=52)  BEGIN /* infrageneric --> subseries */

					SET @GenusSubdivisionEpi = dbo.f_Core_StrSpaceAdd('subser.', @GenusSubdivisionEpi)
				END
				IF (@RankFk=98)  BEGIN /* infrageneric --> tax.infragen. */

					SET @GenusSubdivisionEpi = dbo.f_Core_StrSpaceAdd('t.infgen.', @GenusSubdivisionEpi)
				END
				IF (@RankFk <58 OR @RankFk =98) BEGIN

					SET @NameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@SupraGenericName,@Genus), @GenusSubdivisionEpi), dbo.f_Core_StrSpaceAdd(@SpeciesEpi, @InfraSpeciesEpi))
					SET @FullNameCache = dbo.f_Core_StrSpaceAdd(@NameCache, @AuthorStr)
				END
				IF (@RankFk=58) BEGIN /* aggregate */
					SET @NameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi), 'aggr.') /*species aggregate names do not have author string.*/
					SET @FullNameCache = dbo.f_Core_StrSpaceAdd(@NameCache, @AuthorStr)
				END
				IF (@RankFk=59) BEGIN /* species group */
					SET @NameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi), 'species group') /*species group names do not have author string.*/
					SET @FullNameCache = dbo.f_Core_StrSpaceAdd(@NameCache, @AuthorStr)
				END
				IF (@RankFk = 65) BEGIN /* infraspecific --> subspecies */
					SET @InfraSpeciesEpiTemp = dbo.f_Core_StrSpaceAdd('subsp.', @InfraSpeciesEpi)
				END
				IF (@RankFk = 66) BEGIN /* infraspecific --> subspecies */
					SET @InfraSpeciesEpiTemp = dbo.f_Core_StrSpaceAdd('prol.', @InfraSpeciesEpi)
				END
				IF (@RankFk = 67) BEGIN /* infraspecific --> subspecies */
					SET @InfraSpeciesEpiTemp = dbo.f_Core_StrSpaceAdd('race', @InfraSpeciesEpi)
				END
				IF (@RankFk = 68) BEGIN /* infraspecific --> convarietas */
					SET @InfraSpeciesEpiTemp = dbo.f_Core_StrSpaceAdd('convar.', @InfraSpeciesEpi)
				END
				IF (@RankFk = 70) BEGIN /* infraspecific --> variety */

					SET @InfraSpeciesEpiTemp = dbo.f_Core_StrSpaceAdd('var.', @InfraSpeciesEpi)
				END
				IF (@RankFk = 73) BEGIN /* infraspecific --> subvariety */

					SET @InfraSpeciesEpiTemp = dbo.f_Core_StrSpaceAdd('subvar.', @InfraSpeciesEpi)
				END
				IF (@RankFk = 80) BEGIN /* infraspecific --> forma */

					SET @InfraSpeciesEpiTemp = dbo.f_Core_StrSpaceAdd('f.', @InfraSpeciesEpi)

				END
				IF (@RankFk = 82) BEGIN /* infraspecific --> subforma */

					SET @InfraSpeciesEpiTemp = dbo.f_Core_StrSpaceAdd('subf.', @InfraSpeciesEpi)

				END
				IF (@RankFk = 84) BEGIN /* infraspecific --> forma spec. */

					SET @InfraSpeciesEpiTemp = dbo.f_Core_StrSpaceAdd('f.spec.', @InfraSpeciesEpi)

				END
				IF (@RankFk = 99) BEGIN /* infraspecific --> tax.infrasp. */

					SET @InfraSpeciesEpiTemp = dbo.f_Core_StrSpaceAdd('t.infr.', @InfraSpeciesEpi)

				END
				IF (@RankFk >59 AND @RankFk <>98) BEGIN
					IF (@SpeciesEpi = @InfraSpeciesEpi) BEGIN
						SET @FullNameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi),dbo.f_Core_StrSpaceAdd(@AuthorStr, @InfraSpeciesEpiTemp))
					END ELSE BEGIN
						SET @FullNameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi),dbo.f_Core_StrSpaceAdd(@InfraSpeciesEpiTemp, @AuthorStr))
					END
					SET @NameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@SupraGenericName,@Genus), @GenusSubdivisionEpi), dbo.f_Core_StrSpaceAdd(@SpeciesEpi, @InfraSpeciesEpiTemp))
				END

				IF (@CultivarName IS NULL) AND (@CultivarGroupName IS NULL)AND(@UnnamedNamePhrase IS NULL) BEGIN
					SET @NameCache = @NameCache
					SET @FullNameCache = @FullNameCache
				END
			/*UnamedNamePhrase*/
				IF (@UnnamedNamePhrase IS NOT NULL ) BEGIN
					SET @NameCache = dbo.f_Core_StrSpaceAdd(@NameCache, '"' + @UnnamedNamePhrase + '"')
					SET @FullNameCache = dbo.f_Core_StrSpaceAdd(@FullNameCache, '"' + @UnnamedNamePhrase + '"')
				END

			/* cultivarname and cultivargroupname*/
				IF (@CultivarName IS NOT NULL) AND (@CultivarGroupName IS NOT NULL) BEGIN

					SET @CultivarGroupAndCultivar = dbo.f_Core_StrSpaceAdd('[' + @CultivarGroupName + ' Group]',  CHAR(0x27) + @CultivarName + CHAR(0x27))
					SET @NameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi),@CultivarGroupAndCultivar)
					SET @FullNameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi),@CultivarGroupAndCultivar)

				END ELSE

				IF (@CultivarName IS NOT NULL) AND (@CultivarGroupName IS NULL) BEGIN

					SET @NameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi), CHAR(0x27) + @CultivarName + CHAR(0x27))
					SET @FullNameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi), CHAR(0x27) + @CultivarName + CHAR(0x27))
				END ELSE
				IF (@CultivarName IS  NULL) AND (@CultivarGroupName IS NOT NULL) BEGIN
					SET @NameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi), @CultivarGroupName + ' Group')
					SET @FullNameCache = dbo.f_Core_StrSpaceAdd(dbo.f_Core_StrSpaceAdd(@Genus, @SpeciesEpi), @CultivarGroupName + ' Group')
				END

			END

			/* get the numbers of children if it is a parent of hybridformula */

			SELECT @ChildrenNumber = COUNT(NameFk2) FROM RelName WHERE NameFk1 = @NameId AND (RelNameQualifierFk = 9 OR RelNameQualifierFk = 10)
			If (@ChildrenNumber > 0)  BEGIN

				DECLARE ChildrenCursor CURSOR FOR SELECT NameFk2 FROM RelName WHERE NameFk1 = @NameId AND (RelNameQualifierFk = 9 OR RelNameQualifierFk = 10)

				OPEN ChildrenCursor
				FETCH NEXT FROM ChildrenCursor INTO @ChildrenNameId
				WHILE (@@FETCH_STATUS <>-1) BEGIN
					UPDATE NAME SET NameCache = @NameCache WHERE NameId  = @NameId
					UPDATE NAME SET FullNameCache = @FullNameCache WHERE NameId  = @NameId

					SELECT @OldChildrenNameCache = NameCache,  @OldChildrenFullNameCache = FullNameCache FROM NAME WHERE NameId = @ChildrenNameId
					UPDATE NAME SET NameCache = REPLACE(@OldChildrenNameCache, @OldParentNameCache, @NameCache) WHERE  NameId = @ChildrenNameId
					UPDATE NAME SET FullNameCache = REPLACE(@OldChildrenFullNameCache, @OldParentFullNameCache, @FullNameCache) WHERE  NameId = @ChildrenNameId

					FETCH NEXT FROM ChildrenCursor  INTO @ChildrenNameId
				END
				CLOSE ChildrenCursor
				DEALLOCATE ChildrenCursor

			END ELSE BEGIN
			/*this is the normal case -- not be a parent of hybridformula*/

				UPDATE NAME
				SET NameCache = @NameCache, FullNameCache = @FullNameCache
				WHERE NameId = @NameId

			END
		END
	END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

