sYRsFG9U

1CREATE TABLE [dbo].[dar_pacientes](
2	[IDPaciente] [varchar](50) NOT NULL,
3	[IDUMedica] [nvarchar](50) NOT NULL,
4 CONSTRAINT [PK_dar_pacientes] PRIMARY KEY CLUSTERED 
5(
6	[IDPaciente] ASC
7)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
8) ON [PRIMARY]
9
10GO
11
12CREATE proc [dbo].[spPacientes]    
13as
14begin
15select top 50 
16cp.IDPaciente as paciente_info_curp, 
17cp.IDPaciente as paciente_info_folio,
18cp.IDPaciente as IDPaciente,
19'Mexicana' as paciente_info_nacionalidad,  
20(case cp.ndesconocido when '1' then 'true' else 'false' end) as paciente_info_desconocido,  
21cp.cmaterno  as paciente_info_apellido_materno,  
22cp.cpaterno  as paciente_info_apellido_paterno,  
23cp.dFechaNacimiento  as paciente_info_fecha_nacimiento,  
24cp.cSexo as paciente_info_genero,  
25cp.cNombre as paciente_info_nombre,
26e.cClaveRENAPO  as paciente_info_entidad
27from CTL_Pacientes as cp
28inner join Ctl_Estados e(nolock) on cp.cEdoNacimiento = e.IDEstado
29where not exists (select 1 from dar_pacientes paciente(nolock) where paciente.IDPaciente = cp.IDPaciente)
30end
31GO
32
33IF EXISTS(SELECT ''
34    FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tr_Pacientes_migrados]') AND type = 'TR')
35BEGIN
36DROP TRIGGER[dbo].[tr_Pacientes_migrados]
37END
38GO
39
40CREATE TRIGGER[dbo].[tr_Pacientes_migrados]
41ON[dbo].[CTL_Pacientes] AFTER UPDATE
42as
43begin
44if exists(select *
45    from dar_pacientes dr(nolock) where exists(select * from inserted i where dr.IDUMedica = i.IDUMedica and dr.IDPaciente = i.IDPaciente))
46begin
47delete from dar_pacientes
48where exists(select *
49    from inserted i where dar_pacientes.IDUMedica = i.IDUMedica AND dar_pacientes.IDPaciente = i.IDPaciente)
50end
51end
52GO