localhost: Triggerite loomine

 Процесс, при помощи которого записанные в него действия будут выполнятся автоматически.

Create table linnad(
linnID int PRIMARY KEY identity(1,1),
linnanimi varchar(15),
rahvaarv int);

Create table logi(
id int PRIMARY KEY identity(1,1),
aeg DATETIME,
toiming varchar(100),
sisestatud_andmed TEXT
)

create trigger linnLisamine
on linnad
after insert
as
begin
insert into logi(aeg, toiming, sisestatud_andmed)
select GETDATE(), ‘uus linn on lisatud’,
CONCAT(linnanimi, ‘rahvaarv- ‘, rahvaarv)
from inserted;
END

— proverka trigera

INSERT INTO linnad(linnanimi, rahvaarv)
Values (‘Tallinn’, 650000)
select * from linnad;
select * from logi;

create trigger linnKustutamine
on linnad
after delete
as
begin
insert into logi(aeg, toiming, sisestatud_andmed)
select GETDATE(), ‘linn on kustutatud’,
deleted.linnanimi
from deleted;
END

— proverka trigera
delete from linnad where linnID=1;

select * from linnad;
select * from logi;

alter table logi add kasutaja varchar(100);
select * from logi;

create trigger linnUuendamine
on linnad
for update
as
begin
insert into logi(aeg, kasutaja, toiming, sisestatud_andmed)
select GETDATE(),
user,
‘linna andmed on uuendatud’,
CONCAT(‘vanad andmed: ‘, d.linnanimi, ‘, ‘, d.rahvaarv, ‘uued andmed: ‘, i.linnanimi, ‘, ‘, i.rahvaarv)
from inserted d
inner join inserted i
on i.linnID=d.linnID;
END

— proverka

update linnad set linnanimi=’Tartu’, rahvaarv=100
where linnID=3;
select * from linnad;
select * from logi;

USE [VitaliiSokhan]
GO
/ Object: Trigger [dbo].[linnLisamine] Script Date: 13.03.2024 11:49:00 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[linnLisamine]
on [dbo].[linnad]
after insert
as
begin
insert into logi(aeg, kasutaja, toiming, sisestatud_andmed)
select GETDATE(),
USER,
‘uus linn on lisatud’,
CONCAT(linnanimi, ‘ rahvaarv- ‘, rahvaarv)
from inserted;
END

USE [VitaliiSokhan]
GO
/ Object: Trigger [dbo].[linnKustutamine] Script Date: 13.03.2024 11:49:29 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[linnKustutamine]
on [dbo].[linnad]
after delete
as
begin
insert into logi(aeg, kasutaja, toiming, sisestatud_andmed)
select GETDATE(),
USER,
‘linn on kustutatud’,
deleted.linnanimi
from deleted;
END

USE [VitaliiSokhan]
GO
/ Object: Trigger [dbo].[linnUuendamine] Script Date: 13.03.2024 11:49:51 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[linnUuendamine]
on [dbo].[linnad]
for update
as
begin
insert into logi(aeg, kasutaja, toiming, sisestatud_andmed)
select GETDATE(),
user,
‘linna andmed on uuendatud’,
CONCAT(‘vanad andmed: ‘, d.linnanimi, ‘, ‘, d.rahvaarv, ‘uued andmed: ‘, i.linnanimi, ‘, ‘, i.rahvaarv)
from inserted d
inner join inserted i
on i.linnID=d.linnID;
END