f96579GY

1if not exists (select * from sys.databases where name='PruebaTrigger')
2begin
3	create database PruebaTrigger;
4end
5
6use PruebaTrigger;
7
8if not exists (select * from sys.sysobjects where name='expedientes' and xtype='U')
9begin
10	create table expedientes (
11		code	nvarchar(15) not null,
12		estado	nvarchar(20) default 'INICIO',
13		fechaCambioEstado	datetime,
14		primary key (code) );
15end
16
17DELETE FROM expedientes where code in ('exp1','exp2','exp3');
18insert into expedientes (code) values ('exp1');
19insert into expedientes (code) values ('exp2');
20insert into expedientes (code) values ('exp3');	
21
22if not exists (select * from sys.sysobjects where name='CambioDeEstado' and xtype='U')
23begin
24	create table CambioDeEstado (
25		id	int identity(1,1),
26		code nvarchar(15) not null,
27		estado nvarchar(20) not null,
28		fecha	datetime default GETDATE(),
29		primary key (id) );
30end
31
32if exists (select * from sys.sysobjects where name='FechaCambioEstadoTrigger' and xtype='TR')
33begin
34	drop trigger FechaCambioEstadoTrigger;
35end
36
37if not exists (select * from sys.sysobjects where name='FechaCambioEstadoTrigger' and xtype='TR')
38begin
39	create trigger FechaCambioEstadoTrigger
40	on expedientes
41		after update as 
42			if update (estado)
43			begin
44				update expedientes set fechaCambioEstado=GETDATE() where code=(SELECT code from inserted);
45				insert into CambioDeEstado (code,estado) (select code,estado from deleted where code=deleted.code);
46			end
47end
48
49update expedientes set estado='Inicio',fechaCambioEstado=null where code='exp1'
50
51select * from expedientes
52select * from CambioDeEstado