jr6cuNGb

1IF NOT EXISTS (SELECT * FROM sys.databases where name='PRUEBATRIGGER')
2BEGIN
3	CREATE DATABASE PRUEBATRIGGER;
4END
5
6USE PRUEBATRIGGER
7
8
9IF NOT EXISTS (SELECT * FROM SYS.sysobjects WHERE name='expedientes' and xtype='U')
10BEGIN
11	CREATE TABLE EXPEDIENTES (
12	CODE VARCHAR(15) NOT NULL,
13	ESTADO VARCHAR(20) DEFAULT 'INICIO',
14	FECHACAMBIOESTADO DATETIME,
15	PRIMARY KEY (CODE)
16	);
17END
18
19DELETE FROM EXPEDIENTES WHERE CODE IN ('EXP1','EXP2','EXP3');
20INSERT INTO EXPEDIENTES (CODE) VALUES ('EXP1');
21INSERT INTO EXPEDIENTES (CODE) VALUES ('EXP2');
22INSERT INTO EXPEDIENTES (CODE) VALUES ('EXP3');
23
24IF NOT EXISTS (SELECT * FROM SYS.sysobjects WHERE NAME='HISTORICOEXPEDIENTE' AND xtype='U')
25BEGIN
26	CREATE TABLE HISTORICOEXPEDIENTE (
27	ID INT IDENTITY,
28	CODE VARCHAR(15) NOT NULL,
29	ESTADO VARCHAR(20) NOT NULL,
30	FECHA DATETIME DEFAULT GetDate(),
31	PRIMARY KEY (ID)
32	);
33END
34
35IF OBJECT_ID ('CAMBIODEESTADO','TR') IS NOT NULL
36BEGIN
37	DROP TRIGGER CAMBIODEESTADO;
38END
39
40GO
41
42CREATE TRIGGER CAMBIODEESTADO
43ON EXPEDIENTES
44AFTER UPDATE AS
45	IF UPDATE (ESTADO)
46	BEGIN
47		UPDATE EXPEDIENTES SET FECHACAMBIOESTADO= GetDate() where CODE=(SELECT CODE FROM inserted)
48		INSERT INTO HISTORICOEXPEDIENTE (CODE, ESTADO) (SELECT CODE, ESTADO FROM deleted WHERE CODE=DELETED.CODE)
49	END