Töötaja ülesanne - OLEKSANDR BOHATYROV / PORTFOOLIO

SQL server. Insert table. Tabeli loomine

Introduction

Tiggers are commonly used in SQL Server to automate data updates. This introduction article explains the basics of triggers in SQL, the types of triggers, and how to implement triggers in SQL Server.

What is Trigger in SQL Server?

A SQL trigger is a database object which fires when an event occurs in a database. For example, we can execute a SQL query that will “do something” in a database when a change occurs on a database table, such as when a record is inserted, updated, or deleted. For example, a trigger can be set on a record insert in a database table. For example, if you want to increase the blogs count in the Reports table when a new record is inserted in the Blogs table, we can create a trigger on the Blogs table on INSERT and update the Reports table by increasing the blog count to 1.

Types of Triggers in SQL Server

There are two types of triggers:

DDL Trigger
DML Trigger

DDL Triggers in SQL Server

The DDL triggers are fired in response to DDL (Data Definition Language) command events that start with Create, Alter, and Drop, such as Create_table, Create_view, drop_table, Drop_view, and Alter_table.

Code of a DDL Trigger

CREATE TRIGGER safety
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
    PRINT 'You cannot create, drop, or alter tables in this database'
    ROLLBACK;

SQL

When we create, alter, or drop any table in a database, then the following message appears,

DML Triggers in SQL Server

The DML triggers are fired in response to DML (Data Manipulation Language) command events that start with Insert, Update, and Delete. Like insert_table, Update_view and Delete_table.

CREATE TRIGGER deep
ON emp
FOR INSERT, UPDATE, DELETE
AS
    PRINT 'You cannot insert, update, or delete from this table i'
    ROLLBACK;

SQL

When we insert, update, or delete a table in a database, then the following message appears,

Logon Triggers in SQL Server

In SQL Server, logon triggers are special types of triggers that are designed to execute automatically in response to a user’s logon event. When a user connects to a SQL Server instance, the logon trigger fires before the user’s session is established, allowing you to perform certain actions or enforce specific rules based on the user’s login.

CREATE TABLE dbo.LogonAudit (
    LogonAuditID INT IDENTITY(1,1) PRIMARY KEY,
    LoginName NVARCHAR(50),
    LoginTime DATETIME,
    ClientHost NVARCHAR(50)
);
GO

CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    -- Insert logon event details into an audit table
    INSERT INTO dbo.LogonAudit (LoginName, LoginTime, ClientHost)
    VALUES (ORIGINAL_LOGIN(), GETDATE(), HOST_NAME())
END;
GO

SQL

In this example, we create a logon trigger called LogonAuditTrigger that fires for all logon events on the server. The trigger is configured to execute under the context of the sa account, which requires appropriate permissions.

Within the trigger’s body, we perform an action of inserting logon event details into an audit table called LogonAudit. The ORIGINAL_LOGIN() function retrieves the login name of the user who is attempting to log in. GETDATE() returns the current date and time when the logon occurs. HOST_NAME() retrieves the client host name or IP address from which the logon originates.

By using this logon trigger, each time a user logs in to the SQL Server instance, the trigger will capture and store the login name, login time, and client host information in the LogonAudit table, providing an audit trail of logon events.

Types of DML triggers

There are two types of DML Triggers in SQL Server.

AFTER Triggers

AFTER triggers are executed after the action of an INSERT, UPDATE, or DELETE statement.

create trigger insertt
on emp
after insert
as
begin
insert into empstatus values('active')
end

SQL

INSTEAD Of Triggers

It will tell the database engine to execute the trigger instead of the statement. For example, an insert trigger executes when an event occurs instead of the statement that would insert the values in the table.

CREATE TRIGGER instoftr
ON v11
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO emp
SELECT I.id, I.names
FROM INSERTED I

INSERT INTO emp1values
SELECT I.id1, I.name1
FROM INSERTED I
END

SQL

When we insert data into a view by the following query, it inserts values in both tables.

insert into v11 values(1,'d','dd')

SQL

You can see both tables by the following query.

select * from emp
select * from emp1values

SQL

Summary

In this article, you learn about triggers in SQL and how to implement triggers in SQL Server.

SQL SERVER

Tabeli loomine ja andmete lisamine

CREATE TABLE tootaja (
    tootajaId INT PRIMARY KEY identity (1,1),
    tootaja_nimi VARCHAR(50),
    elukutse VARCHAR(50),
    kogemusi INT,
    tookoht VARCHAR(30)
);

INSERT INTO tootaja (tootaja_nimi, elukutse, kogemusi, tookoht)
VALUES ('Artjom', 'programmeerija', 8, 'Office');
INSERT INTO tootaja (tootaja_nimi, elukutse, kogemusi, tookoht)
VALUES ('Maksim', 'Manager', 5, 'Restoraunt');
INSERT INTO tootaja (tootaja_nimi, elukutse, kogemusi, tookoht)
VALUES ('Yarik', 'Ehitaja', 12, 'Ehitusplats');
INSERT INTO tootaja (tootaja_nimi, elukutse, kogemusi, tookoht)
VALUES ('Luca', 'Õpetaja', 3, 'Kool');
INSERT INTO tootaja (tootaja_nimi, elukutse, kogemusi, tookoht)
VALUES ('Sasha', 'Arst', 10, 'Haigla');

CREATE TABLE logi(
    id int PRIMARY KEY identity (1,1),
    kuupaev DATETIME,
    kasutaja varchar(100),
	andmed TEXT,
	tegevus varchar(100))

Trigerid:

DELETE Triger

CREATE TRIGGER tootajaKustutamine
ON tootaja
AFTER DELETE
AS
BEGIN
    INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
    SELECT GETDATE(), SUSER_SNAME(), 
	CONCAT('tootaja_nimi: ', deleted.tootaja_nimi, ', elukutse: ', deleted.elukutse, ', kogemusi: ', deleted.kogemusi, ', tookoht: ', deleted.tookoht), 'tootaja on kustutatud'
    FROM deleted;
END;

Kontroll:

INSERT Triger

CREATE TRIGGER tootajaLisamine
ON tootaja
FOR INSERT
AS
BEGIN
    INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
    SELECT GETDATE(), SUSER_SNAME(), 
	CONCAT('tootaja_nimi: ', inserted.tootaja_nimi, ', elukutse: ', inserted.elukutse, ', kogemusi: ', inserted.kogemusi, ', tookoht: ', inserted.tookoht), 'tootaja on lisatud'
    FROM inserted;
END;

Kontroll:

Protseduurid:

Uue töötaja lisamine

CREATE PROCEDURE Lisatöötaja
    @tootaja_nimi VARCHAR(50),
    @elukutse VARCHAR(50),
    @kogemusi INT,
    @tookoht VARCHAR(30)
AS
BEGIN
    INSERT INTO tootaja (tootaja_nimi, elukutse, kogemusi, tookoht)
    VALUES (@tootaja_nimi, @elukutse, @kogemusi, @tookoht);
	SELECT * FROM tootaja;
	SELECT * FROM logi;
END;

Kontroll:

Töötaja eemaldamine

CREATE PROCEDURE Eemaldatöötaja
    @tootajaId INT
AS
BEGIN
    DELETE FROM tootaja WHERE tootajaId = @tootajaId;
END;

Kontroll:

Töötajate andmete muutmine

CREATE PROCEDURE MuudaTöötajaAndmeid
    @tootajaId INT,
    @tootaja_nimi VARCHAR(50),
    @elukutse VARCHAR(50),
    @kogemusi INT,
    @tookoht VARCHAR(30)
AS
BEGIN
    UPDATE tootaja
    SET
        tootaja_nimi = @tootaja_nimi,
        elukutse = @elukutse,
        kogemusi = @kogemusi,
        tookoht = @tookoht
    WHERE
        tootajaId = @tootajaId;
	SELECT * FROM tootaja;
	SELECT * FROM logi;
END;

Kontroll:

Lisamine uus tabel Juhataja ja 2 uus pästikud

loomine tabel ja andmete lisamine

CREATE TABLE Juhataja(
    JuhatajaId INT PRIMARY KEY IDENTITY (1,1),
    TootajaId INT,
	FOREIGN KEY (TootajaId) REFERENCES Tootaja(TootajaId),
    institutsioon VARCHAR(50),
    palk DECIMAL(10, 2));
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (2, 'institut 1', 3000.00);
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (4, 'institut 2', 2500.00);
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (5, 'institut 3', 3500.00);
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (1, 'institut 4', 4500.00);
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (3, 'institut 5', 1500.00);

Lisamine triger

CREATE TRIGGER Juhataja_InsertTrigger
ON Juhataja
AFTER INSERT
AS
BEGIN
INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
SELECT GETDATE(), USER, 
CONCAT('institutsioon: ', I.institutsioon, ', palk: ', I.palk, 
      ', tootaja_nimi: ', T.tootaja_nimi, ', elukutse: ', T.elukutse, 
      ', kogemusi: ', T.kogemusi, ', tookoht: ', T.tookoht), 'Juhataja on lisatud'
FROM inserted I
INNER JOIN Tootaja T ON I.TootajaId = T.TootajaId;

Kontroll:

SELECT * FROM Juhataja
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (1, 'institut 6', 2000.00);
SELECT * FROM Juhataja
SELECT * FROM logi

Uuendamine triger

CREATE TRIGGER Juhataja_UpdateTrigger
ON Juhataja
AFTER UPDATE
AS
INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
SELECT GETDATE(), USER, 
CONCAT('institutsioon: ', I.institutsioon, ', palk: ', I.palk, 
        ', tootaja_nimi: ', T.tootaja_nimi, ', elukutse: ', T.elukutse, 
        ', kogemusi: ', T.kogemusi, ', tookoht: ', T.tookoht), 'Juhataja on uuendatud'
FROM inserted I
INNER JOIN Tootaja T ON I.TootajaId = T.TootajaId
INNER JOIN deleted D ON I.JuhatajaId = D.JuhatajaId;

Kontroll:

SELECT * FROM Juhataja
UPDATE Juhataja
SET institutsioon = 'institut 12', palk = 8000.00
WHERE JuhatajaId = 2;
SELECT * FROM Juhataja
SELECT * FROM logi

XAMPP

Tabeli loomine ja andmete lisamine

DELETE triger

INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
SELECT NOW(), USER(),
	CONCAT('tootaja_nimi: ', OLD.tootaja_nimi, ', elukutse: ', OLD.elukutse, ', kogemusi: ', OLD.kogemusi, ', tookoht: ', OLD.tookoht), 'tootaja on kustutatud'

Kontroll:

INSERT triger

INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
SELECT NOW(), USER(), 
	CONCAT('tootaja_nimi: ', NEW.tootaja_nimi, ', elukutse: ', NEW.elukutse, ', kogemusi: ', NEW.kogemusi, ', tookoht: ', NEW.tookoht), 'tootaja on lisatud'

Kontroll:

Protseduurid

Uue töötaja lisamine

BEGIN
    INSERT INTO tootaja (tootaja_nimi, elukutse, kogemusi, tookoht)
    VALUES (nimi, kutse, kogemus, koht);
	SELECT * FROM tootaja;
	SELECT * FROM logi;
END

Kontroll:

Töötaja eemaldamine

BEGIN
    DELETE FROM tootaja WHERE tootajaId = id;
	SELECT * FROM tootaja;
	SELECT * FROM logi;
END

Kontroll:

Töötajate andmete muutmine

BEGIN
    UPDATE tootaja
    SET
        tootaja_nimi = nimi,
        elukutse = kutse,
        kogemusi = kogemus,
        tookoht = koht
    WHERE
        tootajaId = id;
	SELECT * FROM tootaja;
	SELECT * FROM logi;
END

Kontroll:

Lisamine uus tabel Juhataja ja 2 uus pästikud

loomine tabel ja andmete lisamine

CREATE TABLE Juhataja(
    JuhatajaId INT PRIMARY KEY AUTO_INCREMENT,
    TootajaId INT,
	FOREIGN KEY (TootajaId) REFERENCES Tootaja(TootajaId),
    institutsioon VARCHAR(50),
    palk DECIMAL(10, 2));
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (2, 'institut 1', 3000.00);
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (4, 'institut 2', 2500.00);
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (5, 'institut 3', 3500.00);
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (1, 'institut 4', 4500.00);
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (3, 'institut 5', 1500.00);

Lisamine triger

INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
SELECT NOW(), USER(), 
CONCAT('institutsioon: ', NEW.institutsioon, ', palk: ', NEW.palk, 
      ', tootaja_nimi: ', T.tootaja_nimi, ', elukutse: ', T.elukutse, 
      ', kogemusi: ', T.kogemusi, ', tookoht: ', T.tookoht), 'Juhataja on lisatud'
FROM tootaja
INNER JOIN Tootaja T ON NEW.TootajaId = T.TootajaId

Kontroll:

SELECT * FROM Juhataja;
INSERT INTO Juhataja (TootajaId, institutsioon, palk)
VALUES (1, 'institut 6', 2000.00);
SELECT * FROM Juhataja;
SELECT * FROM logi

Juhataja sai 9, sest ma tegin paar viga päästikus ja see kaitses mind nagu oleksin juba kedagi loonud.

Uuendamine triger

INSERT INTO logi (kuupaev, kasutaja, andmed, tegevus)
SELECT NOW(), USER(), 
CONCAT('institutsioon: ', NEW.institutsioon, ', palk: ', NEW.palk, 
        ', tootaja_nimi: ', T.tootaja_nimi, ', elukutse: ', T.elukutse, 
        ', kogemusi: ', T.kogemusi, ', tookoht: ', T.tookoht), 'Juhataja on uuendatud'
FROM tootaja
INNER JOIN Tootaja T ON NEW.TootajaId = T.TootajaId
INNER JOIN Juhataja OLD ON NEW.JuhatajaId = OLD.JuhatajaId

Kontroll:

SELECT * FROM Juhataja;
UPDATE Juhataja SET institutsioon = 'institut 12', palk = 8000.00
WHERE JuhatajaId = 2;
SELECT * FROM Juhataja;
SELECT * FROM logi

Id segi ajada paarist katsest, ma tegin seda kogemata.

TRIGER SQL

What Are SQL Triggers?

In SQL Server, triggers are database objects, actually, a special kind of stored procedure, which “reacts” to certain actions we make in the database. The main idea behind triggers is that they always perform an action in case some event happens. If we’re talking about DML triggers, these changes shall be changes in our data. Let’s examine a few interesting situations:

In case you perform an insert in the call table, you want to update that related customer has 1 more call (in that case, we should have integer attribute in the customer table)
When you complete a call (update call.end_time attribute value) you want to increase the counter of calls performed by that employee during that day (again, we should have such attribute in the employee table)
When you try to delete an employee, you want to check if it has related calls. If so, you’ll prevent that delete and raise a custom exception

From examples, you can notice that DML triggers are actions related to the SQL commands defined in these triggers. Since they are similar to stored procedures, you can test values using the IF statement, etc. This provides a lot of flexibility.

The good reason to use DML SQL triggers is the case when you want to assure that a certain control shall be performed before or after the defined statement on the defined table. This could be the case when your code is all over the place, e.g. database is used by different applications, code is written directly in applications and you don’t have it well-documented.

Types of SQL Triggers

In SQL Server, we have 3 groups of triggers:

DML (data manipulation language) triggers – We’ve already mentioned them, and they react to DML commands. These are – INSERT, UPDATE, and DELETE
DDL (data definition language) triggers – As expected, triggers of this type shall react to DDL commands like – CREATE, ALTER, and DROP
Logon triggers – The name says it all. This type reacts to LOGON events

In this article, we’ll focus on DML triggers, because they are most commonly used. We’ll cover the remaining two trigger types in the upcoming articles of this series.