>Hi Nadya,
>
>(just read this thread...)
>The fastest way "traditional" way would probably be "overindexing" with complex indices.
>This will cost you only a little bit in creating larger cdx file size. There are some objections against creating too many indices and using a column in many times in different index expressions, but since this is a readonly table, most of these are irrelevant.
>
>For instance to walk in the insert case

>[index on upper(ChildTB) + Padl(InsTrigger, 2) tag ChildIns] && create multiple indices at design!!!
>set order to ChildIns
>lcSeekSearch = m.lcSearch + Padl(1, 2)
>if seek(lcSeekSearch)
>  scan while upper(ChildTB) + Padl(InsTrigger, 2) = m.lcSeekSearch for not m.plError
>  endscan
>endif
>

>
>should be the most efficient code when viewed visualing the internal commands vfp has to execute on each run.
>
>BUT sometimes using rushmore IS faster: if vfp has results from reading the cdx on a previous check still cached and decides this cache is current enough so the index isn't read again. This beats the seek()-scan while approach. Even then using only one compound index should be faster than using 2 or more. But when using rushmored "for" take care not to have set an order.

>lcSeekSearch = m.lcSearch + Padl(1, 2)
>scan for upper(ChildTB) + Padl(InsTrigger, 2) = m.lcSeekSearch and not m.plError
>endscan

>
>
>regards
>
>thomas

Hi Thomas,

I actually already switched to using InsCommand/DelCommand/UpdCommand, where I put something like [Restrict_Update(m.luOldKey, m.luNewKey)] and I evaluate this command in run-time. I think, it's along the lines of Gregory's suggestion. So, here is my last version of this code. I have only one index now INDEX ON SearchTB tag SearchTB (I put data in upper case already)
Also I was a little bit afraid to implement Fabio's idea of using IIF() for select correct alias, so I left more lines of code.

if cL_USE_AGAIN
		select (m.lnSelRIDefi)
	else
		select RIDefinitions
	endif

	do case
	case m.lnTriggerType = cnUpdateCode && Update trigger
*	set order to UpdTrig
		scan for SearchTB = m.lcSearch and not empty(UpdCommand) and !m.plError&&and not empty(UpdCommand)
			lcParent = ParentTB
			lcChild = ChildTB
			if m.lcSearch = m.lcParent

				if m.lcParent <> m.lcChild
					if not m.lcParentTag = alltrim(ParentTag)
						lcParentTag  = alltrim(ParentTag)
						lcParentKeyExp = alltrim(ParentKey)
						luOldKey       = oldval(m.lcParentKeyExp, m.lcAlias)
						select (m.lcAlias)
						luNewKey       = evaluate( m.lcParentKeyExp )
						if cL_USE_AGAIN
							select (m.lnSelRIDefi)
						else
							select RIDefinitions
						endif
					endif

				else && very rare case of self-join
					luOldKey = oldval(m.lcChildKeyExp, m.lcAlias)
					select (m.lcAlias)
					luNewKey = evaluate(m.lcChildKeyExp)
					if cL_USE_AGAIN
						select (m.lnSelRIDefi)
					else
						select RIDefinitions
					endif
				endif
			else &&	 m.lcChild = m.lcSearch
				if not m.lcChildTag = alltrim(ChildTag)
					lcChildTag  = alltrim(ChildTag)
					lcChildKeyExp = alltrim(ChildKey)
					luOldKey       = oldval(m.lcChildKeyExp, m.lcAlias)
					select (m.lcAlias)
					luNewKey       = evaluate( m.lcChildKeyExp )
					if cL_USE_AGAIN
						select (m.lnSelRIDefi)
					else
						select RIDefinitions
					endif
				endif
			endif

			if m.luNewKey <> m.luOldKey && Check for IsNull is embedded indirectly)
				=evaluate(alltrim(UpdCommand))
			endif
		endscan

	case m.lnTriggerType = cnInsertCode  && Insert trigger
*	set order to InsTrig

		scan for SearchTB = m.lcSearch and not empty(InsCommand) and !m.plError && and not empty(InsCommand)

			if vartype(m.pcCascadeParent) <> "C" or m.pcCascadeParent <> alltrim(ParentTB)

				if m.lcChildKeyExp <> alltrim(ChildKey) && we don't want to evaluate more times than needed
					lcChildKeyExp = alltrim(ChildKey)
					select (m.lcAlias)
					luNewKey  = evaluate(m.lcChildKeyExp)
					if cL_USE_AGAIN
						select (m.lnSelRIDefi)
					else
						select RIDefinitions
					endif
				endif

				if !isnull(m.luNewKey)
					=evaluate(alltrim(InsCommand))
				endif
			endif
		endscan

	case m.lnTriggerType = cnDeleteCode && Delete trigger
*	set order to DelTrig
		scan for SearchTB = m.lcSearch and not empty(DelCommand) and not m.plError &&and not empty(DelCommand)
			if not m.lcParentTag = alltrim(ParentTag)
				lcParentTag  = alltrim(ParentTag)
				lcParentKeyExp = alltrim(ParentKey)
				select (m.lcAlias)
				luKey       = evaluate( m.lcParentKeyExp )
				if cL_USE_AGAIN
					select (m.lnSelRIDefi)
				else
					select RIDefinitions
				endif
			endif

			if not isnull(m.luKey)
				=evaluate(alltrim(DelCommand))
			endif
		endscan
	endcase