This SQL code is for making two tables in a database. These tables can be used for keeping track of cars and their owners, and for logging different actions with these cars.
Autoregister- is for keeping info about registered cars.
Create table autoregister(
id INT PRIMARY KEY IDENTITY(1,1),
number varchar(15),
mark varchar(100),
varv varchar(50),
omanik varchar(200));
- IDENTITY(1,1) means the values will automatically generate, starting from 1 and increasing by 1 with each new entry
- number – a string with a maximum length of 15 characters
- mark – a string, but with a max len of 100 characters
- varv (color) – a string with a max len of 50 characters
- omanik (owner) – a string with a max len of 200 characters
Table “logitabel”: is for keeping logs or records of different actions done with the cars.
Create table logitabel(
id INT PRIMARY KEY IDENTITY(1,1),
aeg DATETIME,
toiming varchar(100),
autoAndmed TEXT);
- aeg (time) – data type DATETIME
- toiming (action) – description of the action, a string with a max len of 100 characters
- autoAndmed (car data) – data about the car related to the action. The data type TEXT allows storing large text data
phpMyAdmin:
Create table autoregister(
id int AUTO_INCREMENT PRIMARY KEY,
number varchar(15),
mark varchar(100),
varv varchar(50),
omanik varchar(200));
Create table logitabel(
id int AUTO_INCREMENT PRIMARY KEY,
aeg DATETIME,
toiming varchar(100),
autoAndmed TEXT);
Auto lisamine:
Creating a trigger named ‘autoLisamine’ for the table ‘autoregister’
CREATE TRIGGER autoLisamine
ON autoregister
AFTER INSERT
AS
BEGIN
-- Inserting a new record into 'logitabel' table
INSERT INTO logitabel(aeg, toiming, autoAndmed)
-- Setting the current date and time for the log, the action as 'Car added'
-- concatenating car details into a string for the 'autoAndmed' field
SELECT GETDATE(), 'Auto on lisatud',
CONCAT('Auto number: ', number, ', Mark: ', mark, ', Värv: ', varv, ', Omanik: ', omanik)
FROM inserted;
-- 'inserted' is a special table used in triggers that holds the newly inserted rows
END
This is a check section to insert a new car into ‘autoregister’ and see the trigger in action
Inserting a new car record into ‘autoregister’ table
Selecting all records from ‘autoregister’ to check the inserted car record and from ‘logitabel’ to check the log entry
INSERT INTO autoregister(number, mark, varv, omanik)
Values ('910 NJY', 'Toyota', 'gray', 'DS');
SELECT * FROM autoregister;
SELECT * FROM logitabel;
Auto Kustutamine:
Creating a trigger named ‘autoKustutamine’ for the ‘autoregister’ table
CREATE TRIGGER autoKustutamine
ON autoregister
AFTER DELETE
AS
BEGIN
-- Inserting a new record into 'logitabel' table
INSERT INTO logitabel(aeg, toiming, autoAndmed)
-- Setting the current date and time for the log
SELECT GETDATE(), 'Auto on kustutatud',
deleted.number
FROM deleted;
-- 'deleted' is a table used in triggers that holds the deleted rows
END
DELETE FROM autoregister Where id = 4;
SELECT * FROM autoregister;
SELECT * FROM logitabel;
Tabeli Uuendamine:
Adding a new column ‘kasutaja’ to the ‘logitabel’ table to store the username of the user performing the action
ALTER TABLE logitabel ADD kasutaja varchar(100);
SELECT * FROM logitabel;
Creating a trigger named ‘autoUuendamine’ on the ‘autoregister’ table
CREATE TRIGGER autoUuendamine
ON autoregister
FOR UPDATE
AS
BEGIN
INSERT INTO logitabel(aeg, kasutaja, toiming, autoAndmed)
SELECT GETDATE(),
-- USER - to get the username of the person performing the update
USER,
-- Specifying the action as 'Car data updated'
'Auto andmed on uuednatud',
CONCAT('Vanad andmed: ', d.number, ', ' , d.mark, ', ' , d.varv, ', ' , d.omanik,
'Uued andmed: ', i.number, ', ' , i.mark, ', ' , i.varv, ', ' , i.omanik)
FROM deleted d
-- Joining 'deleted' and 'inserted' to compare old and new values
INNER JOIN inserted i
ON i.id = d.id;
END
This command updates the record with id = 2 in the “autoregister” table
UPDATE autoregister SET number = '528 HDE', mark = 'BMW', varv = 'white', omanik = 'Deniss'
WHERE id = 2;
SELECT * FROM autoregister;
SELECT * FROM logitabel;
