Welcome to Appsloveworld Technologies. During this session we will learn that I strongly recommend you to read the following articles in this series instead of removing the trigger before continuing with the recording.
There are three types of DML, DDL and connection triggers in the SQL server.
DML triggers are triggered in response to DML events. The DML examples even include insertion of updates and deletion.
DML triggers can be divided into two types, depending on the trigger and the location.
After activation, the shot is automatic when the trigger is pressed, while the shot is fired instead of the trigger.
Instead of deleting the release C Example
We now know that triggers are generally very useful. If you have a multi-table view and you want to insert data into that view or update the view with the same rows instead of a delete button, it is also used to delete rows from the multi-table view.
So, we have a TblSpecialization and TblPhysicians table, TblSpecialization has columns called Id and Specialization, we have a TblPhysicians table that has columns called Id, SpecializationId, with names, gender and SpecializationId.
If you look at these two tables, the common column between them is the Specialization column.
On the basis of these two tables, we have created an image that reflects the name of the gender specialisation.
Therefore the column Specialization Name is taken from table TblSpecialization and the column Name and Gender from table TblDocs.
Create an image of [dbo] [vWDoctorsInformation]
as
Select TblDoctors.Id,DoctorName,Gender,Specialization
of TblDoctors
add to TblSpecialization
on TblDoctors.SpecializationId = TblSpecialization.Id.
It is therefore obvious that this position is based on these two tables, TblSpecialisation and TblMédecins. Now suppose someone tries to remove from the view, then it is clear that he is trying to remove from the view where the ID 8 is located.
Delete from [vWDoctorsInformation], where Id = 8.
This position is based on these two tables, TblSpecialisation and TblMédecins. Now suppose someone tries to remove from the view, then it is clear that he is trying to remove from the view where the ID 8 is located.
So there was a mistake. Do you really want to take Henry’s file or the cancer treatment file off the Tbl specialty board?
As a result, SQL Server will not allow changes that affect multiple basic tables. So you try to update this view or delete a line in this view, and this deletion affects different basic tables, so you get an error when you try to run an operator.
The view of function vWDoctorsInformation is not updated because the change affects different basic tables.
Okay, now in this view, let’s create a remove trigger instead of a remove trigger, so here’s the remove trigger we create.
Instead of deleting the Trigger Sql Example:
Create the trigger tr_vWDoctorsInformation_InsteadOfDelete
on vWDoctorsInformation
instead of deleting
as
Start
Delete
on TblDoctors
with the
deleted on TblDoctors.Id = deleted.Id
Termination
Now let’s remove the disk from sight…
Delete from [vWDoctorsInformation] where Id = 8
, select * from [vWDoctorsInformation].
As you can see in the figure above, the record with Id=8 was successfully deleted.
Note above Instead of removing the trigger of motion
we create this trigger, a meaningful trigger name on vWDoctorsInformation, and you create it instead of removing it.
Therefore, this trigger should be activated instead of a deletion statement as a start and end point. Then we join the TblDoctors with the table removed. So we talked about taking over the previous position in this series. If you are a newcomer, please read the message below.
So, what we do here is know that triggers use special tables called insertion and deletion tables.
So, what we do here is know that triggers use special tables called insertion and deletion tables.The inserted table contains the newly inserted data. And that’s what we were talking about when we talked about instead of inserting a trigger, and when we use a trigger instead of updating, deleting and inserting tables, both are used.
The deleted table contains data before the update, i.e. old data, and the inserted table contains new data after the update.
So, instead of updating, it uses both tables, and instead of deleting, it only uses the table that has been deleted.
Therefore, the deleted table here contains only the deleted items, and the inserted table is essentially empty if you use Delete instead of the trigger.
Also, instead of inserting a deletion, the table will be empty because you only insert a row so that the newly inserted row exists in this inserted table.
So what we do here is merge the tables remotely, because if you delete five entries, all five entries will be in the table remotely.
we take all identifiers from the deleted table, add them to TblDoctors and remove the employee from the table. So you do it with the Joints.
And another way to do this is by using subquery.
Create trigger tr_vWDoctorsInformation_InsteadOfDelete
on vWDoctorsInformation
instead of deleting
as
Start
– Subquery
Delete from TblDoctors
where Id is in (Select Id deleted from)
Termination
And if you look at the subquery at the top of the trigger, it is very easy to understand that everything you say here is to remove the ID from the table removed from the TblDoctors where the ID is.
So you copy the table’s ID remotely. All identifiers that exist in the remote table in the subquery.
And then these are identifiers that are removed from the TblDoctors table. It is therefore clear that this sub-question is easy to understand by simply looking at it.
They are very easy to read, but in terms of performance, most connections are faster than some requests.
In some real-life cases, however, only a subset of the table entries is needed to make sub-questions faster.
However, you only need two or three records in this table. And there’s a condition where you select those two or three, so use a subquery in those cases because you can’t connect to every line in this huge table.
You know, if you select these channels with a sub request, your request will be much faster than with the join. But they’re very rare. But we usually use Jones.
When you use subqueries, SQL Server converts them into joins, and it is generally better to use joins than subqueries.
Table Scenario
/****** object: Table [dbo]. Date of the scenario : 10/15/2020 10:35:34 ******/
ANSI_ZERO SETTINGS ON
GO
QUOTED_IDENTIFIER SETTINGS ON
GO
CREATE TABLE [dbo].TblDoctors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DoctorName] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[SpecializationId] [int] NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEUSTERED
(
[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON
GO
/****** Subject : Table [dbo]. Date of the scenario : 10/15/2020 10:35:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCATE TABLE [dbo].TblSpecialization ]([Id] [int] NOT NULL, [Specialization] [nvarchar](max) NULL,CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED([Id] ASC)WITH (PAD_INDEX = OFF), STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GOSET IDENTITY_INSERT [dbo]. UNGOINSERT [dbo]. [TblDoctors] ([Id], [name of doctor], [gender], [specialization]) Prizes (1, ‘Carter’, ‘Man’, 1)GOINSERT [dbo].TblDoctors]. ([Id], [name of doctor], [gender], [specialization]) Prizes (3, ‘Gianna’, ‘Woman’, 1)GOINSERT [dbo]. ([Id], [name of doctor], [gender], [specialization]) Prizes (4, ‘Brandon’, ‘Man’, 4)GOINSERT [dbo].Tbl doctors]. ([Id], [name doctor], [gender], [specialization]) PRIZE (5, ‘Julia’, ‘Woman’, 7)GOINSERT [dbo]. [Tbl doctors] ([Id], [name of doctor], [gender], [specialization]) PRICES (6, ‘Julian’, ‘Male’, 6)GOINSERT [dbo].TblDoctors] ([Id], [name of doctor], [gender], [specialization]) PRICES (7, ‘Kayla’, ‘Woman’, 5)GOINSERT [dbo]. PRICES (9, fall, female, 1) GOINSERT [dbo].TblDoctors] ([Id], [name of doctor], [gender], [specialization]) PRICES (10, ‘Sebastian’, ‘man’, 2)GOINSERT [dbo]. ([Id], [name doctor], [gender], [specialization]) PRIZE (11, ‘Blake’, ‘Male’, 3)GOINSERT [dbo]. ([Id], [name of doctor], [gender], [specialization]) RATES (12, Dr. Sebastian, Man, 2)GOINSERT [dbo]. [TblDoctors] ([Id], [name of doctor], [gender], [specialization]) PRICES (13, ‘Dr. Henry’, ‘Man’, 5) GOINSERT [dbo]. ([Id], [name doctor], [gender], [specialization]) Prize (14, ‘Dr Jacob’, ‘Man’, 4)GOINSERT [dbo’. Tanmay, man, 1)GOSET IDENTITY_INSERT [dbo]. OFFGOINSERT [dbo]. TblSpecialization] ([Id], [specialization]) PRICE (1, ‘Neurology’)GOINSERT [dbo]. TblSpecialization] ([Id], [Specialization]) PRICE (2, ‘Urology’) GOINSERT [dbo]. TblSpecialization] ([Id], [Specialization]) PRICE (3, Gastroenterological Surgery) GOINSERT [dbo]. [ TblSpecialist ] ([Id], [specialization]) VALUES (4, Cardiology (cardiac care)) GOINSERT [dbo]. TblSpecialisation] ([Id], [specialisation]) VALUES (5, ‘Oncology (care for cancer patients)’) GOINSERT [dbo]. TblSpecialization] ([Id], [Specialization]) VALUES (6, ‘Bone Marrow Transplantation’) GOINSERT [dbo]. TblSpecialization] ([Id], [Specialization]) VALUES (7, Medical Gastroenterology.)GO
Related Tags:
how technology helps relationships,does technology ruin relationships,internet breaks relation,technology and socialization,positive effects of technology,disadvantages of technology,what is technology,how technology has changed our relationships,love in the digital age online,impact of technology on love,love in the digital age cast,how to adapt to change in a relationship,love technology,is love losing its soul in the digital age,technology killing romance,how does technology help friendships,technology and human relationships,benefits of technology in relationships,positive effects of technology on relationships,technology and relationships,the impact of technology on human communication and relationships,technology has improved our personal connections,how has technology affected romantic relationships