-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathtriggers II.sql
132 lines (115 loc) · 3.01 KB
/
triggers II.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
use Negocios
go
--Auditar tabla paises
--paso 01 : crear la tabla auditoria donde la registrará las transacciones de la tabla Pais
create table auditaPais(
usuario varchar(50),
host varchar(50),
fecha datetime,
tipoTran varchar(50),
newRecord text,
oldRecord text
)
go
-----------------------------------
select * from Ventas.paises
select * from auditaPais
go
--Paso 02 :Crear el trigger
create trigger tr_autitapais
on ventas.paises
for insert , delete , update
as
begin
Declare @v_tipoTransaccion varchar(max),
@v_newRecord varchar(max), @v_olRecord varchar(max)
if exists (select * from inserted) and exists (select * from deleted)
begin
set @v_tipoTransaccion='Actualizacion de Datos'
set @v_newRecord=(select Idpais+space(1)+NombrePais from inserted)
set @v_olRecord=(select Idpais+space(1)+NombrePais from deleted)
end
else if exists (select * from inserted)
begin
set @v_tipoTransaccion='Insercion de registro'
set @v_newRecord=(select Idpais+space(1)+NombrePais from inserted)
set @v_olRecord=null
end
else
begin
set @v_tipoTransaccion='Eliminacion de Registro'
set @v_newRecord=null
set @v_olRecord=(select Idpais+space(1)+NombrePais from deleted)
end
insert into auditaPais
values
(USER_NAME(), HOST_NAME(), GETDATE(), @v_tipoTransaccion, @v_newRecord, @v_olRecord)
end
go
--Comprabando la efectividad del trigger
--Insercion
set nocount on
insert Ventas.paises
values
('10','Costa Rica')
go
--Actualizacion
update Ventas.paises
set NombrePais='Peru'
where Idpais='200'
go
--Eliminacion
delete from Ventas.paises
where Idpais='200'
go
----------------------------------------
--Crear auditoria a la tabla producto
create table auditaProducto(
usuario varchar(50),
host varchar(50),
fecha datetime,
tipoTran varchar(50),
newRecord text,
oldRecord text
)
go
select * from Compras.productos
select * from auditaProducto
go
create trigger tr_autoriaProducto
on Compras.productos
for insert , delete , update
as
begin
Declare @v_tipoTransaccion varchar(max),
@v_newRecord varchar(max), @v_olRecord varchar(max)
if exists (select * from inserted) and exists (select * from deleted)
begin
set @v_tipoTransaccion='Actualizacion de Datos'
set @v_newRecord=(select cast(IdProducto as char(4))+space(1)+NomProducto from inserted)
set @v_olRecord=(select cast(IdProducto as char(4))+space(1)+NomProducto from deleted)
end
else if exists (select * from inserted)
begin
set @v_tipoTransaccion='Insercion de registro'
set @v_newRecord=(select cast(IdProducto as char(4))+space(1)+NomProducto from inserted)
set @v_olRecord=null
end
else
begin
set @v_tipoTransaccion='Eliminacion de Registro'
set @v_newRecord=null
set @v_olRecord=(select cast(IdProducto as char(4))+space(1)+NomProducto from deleted)
end
insert into auditaProducto
values
(USER_NAME(), HOST_NAME(), GETDATE(), @v_tipoTransaccion, @v_newRecord, @v_olRecord)
end
go
update Compras.productos
set NomProducto='Holes'
where IdProducto='3'
go
delete from Compras.productos
where IdProducto=2
go