if exists (select name from sysobjects where name = 'td_ensespecialidades')
drop trigger td_ensespecialidades;
go
exec('create trigger td_ensespecialidades on ensEspecialidades for delete as
begin
declare
@numrows int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
if @numrows = 0
return
/* DeleteParentRestrict: Impossivel excluir pai "ensEspecialidades" pois existem registros na filha "ensGrades_Profissionais" */
if exists (select 1 from ensGrades_Profissionais t2, deleted t1
where t2.especialidade = t1.especialidade)
begin
select @errno = 50006,
@errmsg = ''Existem registros em "ensGrades_Profissionais". Impossível excluir "ensEspecialidades".''
goto error
end
/* DeleteParentCascade: apaga todas as filhas "ensPolos_Especialidades" da Pai "ensEspecialidades" */
set nocount on
delete ensPolos_Especialidades
from ensPolos_Especialidades t2, deleted t1
where t2.especialidade = t1.especialidade
set nocount off
/* DeleteParentCascade: apaga todas as filhas "ensEspecialidades_Profissionais" da Pai "ensEspecialidades" */
set nocount on
delete ensEspecialidades_Profissionais
from ensEspecialidades_Profissionais t2, deleted t1
where t2.especialidade = t1.especialidade
set nocount off
/* DeleteParentCascade: apaga todas as filhas "ensRegioesAdm_ValoresPadrao" da Pai "ensEspecialidades" */
set nocount on
delete ensRegioesAdm_ValoresPadrao
from ensRegioesAdm_ValoresPadrao t2, deleted t1
where t2.especialidade = t1.especialidade
set nocount off
/* DeleteParentSetNull: Exclusão de registro na pai "ensEspecialidades" deixa null a chave na filha "ensProfissionais" */
set nocount on
update ensProfissionais
set especialidade_principal = NULL
from ensProfissionais t2, deleted t1
where t2.especialidade_principal = t1.especialidade
set nocount off
return
error:
raiserror @errno @errmsg
rollback transaction
end
')
----------------------------------------
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
This award recognizes an author who contributes the highest volume of original works or content. Recipients of this award produce extremely valuable content that prioritizes accuracy, relevancy, and professionalism.