Welcome to Appsloveworld Technologies, In the session, we learn about instead of delete trigger before continuing with the post, I strongly recommend to read below the post of this series.
- DML triggers ,After Insert,After Delete Trigger with examples
- After Update Trigger in Sql Server With Example
- Instead Of Insert Triggers in Sql server With Example
- Instead Of Update Trigger In Sql Server With Example
In the SQL server, there are three types of triggers DML, DDL, and Log On triggers.
DML Triggers are fired in response to DML events. Examples of DML even include insert update and delete.
DML Triggers can further be classified into two types after triggers and instead of triggers.
After triggers are fired automatically after the triggering action, whereas instead of triggers are fired instead of the triggering action.
Instead of delete trigger With Example
Now we know that in general, instead of triggers, are extremely useful. Whenever you have a view that’s based on multiple tables and if you want to either insert data into it or update View along the same lines instead of delete trigger is also used to delete a row from a view that’s based on multiple tables.
So we have TblSpecialization and TblDoctors table here, TblSpecialization has got Id and Specialization name columns, and we have TblDoctors Table, which has got Id, name, gender, SpecializationId Columns.
If you look at these two tables, the common column between them is the SpecializationId columns.
Based on these two tables, we have created a view which returns the name gender Specialization name.
so the Specialization name column is coming from TblSpecialization, and Id name and gender are coming from TblDoctors table.
Create view [dbo].[vWDoctorsInformation]
as
Select TblDoctors.Id,DoctorName, Gender, Specialization
from TblDoctors
join TblSpecialization
on TblDoctors.SpecializationId = TblSpecialization.Id
So obviously this view is based on these two based tables, TblSpecialization and TblDoctors. Now let us say somebody is trying to delete from The View, so obviously, delete from view where ID is equal to 8.
Delete from [vWDoctorsInformation] where Id = 8
This view is based on these two based tables, TblSpecialization and TblDoctors. Now let us say somebody is trying to delete from The View, so obviously, delete from view where ID is equal to 8.
So there is a confusion. Do you want to actually delete Henry’s record or the Oncology (Cancer Care) record from the TblSpecialization table?
So SQL server will actually not allow any modification that affects multiple base tables. So you are trying to update this view or delete a row from this view and that delete is going to affect multiple base tables.so you when try to execute the statement, you will get an error.
View or function ‘vWDoctorsInformation’ is not updatable because the modification affects multiple base tables.
So now let’s create an instead of delete trigger on this view, So here is the instead of delete trigger that we are creating.
Instead of delete trigger Sql Example:
Create Trigger tr_vWDoctorsInformation_InsteadOfDelete
on vWDoctorsInformation
instead of delete
as
Begin
Delete TblDoctors
from TblDoctors
join deleted
on TblDoctors.Id = deleted.Id
End
Now let’s delete record from the view
Delete from [vWDoctorsInformation] where Id = 8
select * from [vWDoctorsInformation]
As you can see in the above image, record with Id=8 is deleted successfully.
Explanation of above Instead of delete trigger Query
we are creating this trigger create, a meaningful trigger name on vWDoctorsInformation and you are creating instead of delete trigger.
So this trigger should be fired instead of the delete statement as Begin and End. Then we are joining TblDoctors with the deleted table. So we have spoken about joins in the previous post of this series. So if you new to join, please read the below post.
So what we are doing here is basically we know that triggers make use of special tables called inserted and deleted tables.
The inserted Table will contain the newly inserted data. And we have spoken about this when we, discussed instead of insert trigger and when we use instead of update Trigger ,deleted and inserted table both are used.
The deleted table contains the data before the update, which means old data and the inserted table contains the new data after the update.
So instead of update makes use of both the tables, whereas instead of delete, make use of only the deleted table.
So here the deleted table contains only the deleted records and inserted a table will basically be empty when you are making use of instead of delete trigger.
Similarly, for instead of insert the delete the table will be empty because you are only inserting a row so that the newly inserted row will be present in that inserted table.
so what we’re doing here, we are joining the deleted tables because if you delete five records, all those five records will be there in the deleted table.
we are taking all the IDs from the deleted table, joining them at TblDoctors, and deleting them from table employee. So you are doing that using joins.
And another way to do it is actually to use a subquery
Create Trigger tr_vWDoctorsInformation_InsteadOfDelete
on vWDoctorsInformation
instead of delete
as
Begin
--Subquery
Delete from TblDoctors
where Id in (Select Id from deleted)
End
And if you look at above subquery in trigger, it’s very simple to understand all you are saying here is delete from TblDoctors where ID in, select id from, deleted table.
So you’re taking the ID from the deleted table. All the Id that is present in the deleted table within the subquery.
And then those are the Id’s that are being deleted from the TblDoctors table. So obviously this subquery is easy to understand just by looking at that.
They are very simple to read, but then from a performance point in most of the cases, joins are faster than some queries.
But however, there are real cases, you know, when you only need a subset of records from the table that you are joining with subqueries can be faster.
But then in that table, you only want two or three records. And there is a condition based on which you are selecting those two or three, So in those cases, use a subquery because you are not joining with every row in that huge table.
You know, when you select those rows using the subquery, your query will be much faster than using join. But these are very rare. But in general, we use Joins.
In fact, when you use subqueries, SQL Server converts them into the join, in general it’s better to use Joins over subqueries.
Table Script
/****** Object: Table [dbo].[TblDoctors] Script Date: 10/15/2020 10:35:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER 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 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]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[TblSpecialization ] Script Date: 10/15/2020 10:35:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TblDoctors] ON
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (1, 'Carter', 'Male', 1)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (3, 'Gianna', 'Female', 1)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (4, 'Brandon', 'Male', 4)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (5, 'Julia', 'Female', 7)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (6, 'Julian', 'Male', 6)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (7, 'Kayla', 'Female', 5)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (9, 'Autumn', 'Female', 1)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (10, 'Sebastian', 'Male', 2)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (11, 'Blake', 'Male', 3)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (12, 'Dr. Jacob', 'Male', 4)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (13, 'Dr. Henry', 'Male', 5)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (14, 'Dr. Tanmay', 'Male', 1)
GO
SET IDENTITY_INSERT [dbo].[TblDoctors] OFF
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (1, 'Neurology')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (2, 'Urology')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (3, 'Surgical Gastroenterology.')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (4, 'Cardiology (Heart Care)')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (5, 'Oncology (Cancer Care)')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (6, 'Bone Marrow Transplant.
')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (7, 'Medical Gastroenterology.
')
GO
The post Instead Of Delete Trigger In Sql Server With Example appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- "AND" operation execution methodology in SQL Server
- Change SQL SERVER 2005 from default instance to named instance?
- How to support column names with spaces using sqoop import?
- Can I have multiple rows of tabs in SQL Server Management Studio
- Cannot find data type boolean
- SQL many-to-many design
- SQLAlchemy : FOR XML PATH equivalent
- How to merge rows of SQL data on column-based logic?
- How to Delete Duplicate records from Table in SQL Server?
- Update a column by comparing two different columns in the same table
- Remove duplicate depending on Date value
- Query with dynamic columns and a count on each column
- Can't select more than 700000 rows from SQL Server using C#
- What indenting style do you use in SQL Server stored procedures?
- IN NOT IN SQL Server 2005
- Insert consecutive numbers into a table
- 'Login Failed for User' with Successful Test Connection
- SQL The multi-part identifier "AVG.Hours" could not be bound
- How to copy records to another table based on a condition?
- SQL table JOIN on difference of two tables
- SQL Powershell Error: Invoke-Sqlcmd : The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet
- why are asymmetric keys more secure?
- SQL server Calculate with COUNT
- order different columns into one order?
- How to get the last run job details in SQL
- Get average in SQL ignoring all values less than 0
- SQL Query on single table-valued parameter slow on large input
- Roles of parentheses in SQL Server SELECT queries?
- Alternative to DISTINCT Function
- Optimize a sports site standings query
- How do I structure this transaction?
- SQL Exception Timeout: Does the query complete?
- Why is my SQL Query only selecting the last entry in a column
- how to pass argument in console application which having space using c#
- Why is a query under a IF statement that is false running?
- Join two table by dates and populate the missing data when one table has missing dates
- Sql Server 2016 Performance is terrible doing very simple inserts
- BulkCopy Vs BCP Insert
- Parsing insert queries from SQL Server
- How to modify multiple nodes using SQL XQuery in MS SQL 2005
- Transact SQL - which Join to Use
- extract data from pipe delimited String
- Calculate totals in product matrix
- How can I correctly order by this varchar field representing a multiple level order using SQL?
- How to work "script Table as" , "Create table" script in sql management studio
- Calling Stored Procedure on Table Update T-SQL
- Inefficient LINQ - Know What SQL Should Be - Can't get there
- How Does One Fill a Typed DataSet, Keep it Synchronized, and Receive Updates When the Data Changes?
- Broad thoughts on XML nodes to SQL through datatables?
- How to find list of tables that are having foreign key contraint for primary key of Table-A?
- Convert Rows to column rows in SQL Server
- Fetching multiple rows by foreign key column
- Getting Sheet Name From Excel
- How to add additional navigational properties that won't be persisted?
- Returning largest value across columns in SQL Server 2008
- modify database after time limit
- Return result based on multiple values AND set value based on result
- How to verify if a row has been deleted in SQL server
- Can't Get Codeigniter Count Result to Work
- Renaming a column in MS SQL Server 2005
- SQL Server RowVersion
- SQL Query Group By inclusion
- Finding unique combinations of columns
- When updating a row, how to also keep the old values and update the linking tables?
- Transient errors during SQL Server failovers
- How to automate the process of restore db in SQL server?
- Combining (concatenating) date and time into a datetime
- Subquery returns more than 1 value SQL error on multiple selection
- How to get time value for multiple max/min values in query
- Did the ordering of the returned columns change for select * for SQL Server 2012?
- how to split one column into two and group by id
- Connect to SQL Server using PHP?
- Detecting dirty reads from a stored procedure
- Repeat Event Calendar based - Database Design
- Retrieving large amount of records from Database
- SQL Server 2014 - Want to JOIN, but records may not be on one table or the other
- SQL SERVER count distinct using Offset
- How can I test stored procedure errors in SQL Server
- Can I add a FK column of same table?
- How to send SSRS Report Path in a parameter to the Stored Procedure?
- Best way of logging in SSIS
- Counting the number of rows based on like values
- how to change column name and change a value in a table via a stored procedure?
- Continuous delivery of SQL Database to Windows Azure by using Team Foundation Service
- SQL Management Studio show network drives
- Now and Next TV Programme Information SQL Query
- Indicating primary/default record in database
- sql most recent two records filtered on date
- Entity Framework added 3 foreign keys for 1 relationship
- How can I store more than 4000 chars in an SQL Server parameter?
- How to SQL One to Many Relationship?
- T-SQL - select XML node values, or NULL if the nodes don't exist
- Stopping .NET app's SQL Server DB being modifiable via Access using ODBC?
- SQL Query data splitting
- SQL ContainsTable is ranking higher if word is found multiple times. How do I stop this?
- How to actually unset (set to Nothing) COM-object property in Powershell?
- Create table with distinct values based on date
- Efficiently select nested dependency tables and multiple columns in a single row using join in SQL server
- Sum multiple column with PARTITION from single table
- Enforce referential integrity in MS Access vs Foreign Key Constrains in Microsost SQL Server Management Studio?
- How to update a table according records in another table
- Case query based on two input values
- Simple, ordinary SQL Server insert method
- Where and how do some folders on the server get converted into URL adresses?
- Does Oracle have a filtered index concept?
- SQL server root Directory
- TSQL Cursor automatically rounds up money type variables
- MSSQL pattern to match all numbers between [1-999]
- Multi-part identifier could not be bound?
- SQL Server : string to rows with delimiter?
- How to get values from a XML where the names are the same
- SQL ISDATE returns false for string formatted as ISO w/timezone (127)
- Using a database API cursor with JDBC and SQLServer to select batch results
- TSQL, How can I group rows?
- How can I set the primary key value to other col in table when I insert a new row in SQL Server
- What is the most efficient way to calculate running totals for consumption in Sql Server Analysis Services?
- EF Reverse POCO against a SQL Database Project?
- Simple if statement not working (string comparison)
- Run stored procedure for each value in a comma separated string
- search for a specific value from multiple columns in a table
- Tools for website/web application load testing?
- How to write query/trigger that satisfies following scenario?
- Unable to see database diagram option in SQL Server
- Failed to Login as 'Domain\ComputerName' pyodbc with py2exe
- Combining Columns With Different Filters
- Joining two table with condition
- Better approach to write query with group by
- SQL Server index design where PK would be same size as row data
- update query based in values from the subquery
- Is it possible to export 'PRINT' commands into a file via bcp?
- T-SQL NOT IN works, but NOT EXISTS does not
- SQL Server 2005 and APP_DATA
- How do I return NULL conditionally from a JSON_QUERY
- NHibernate.Exceptions.GenericADOException Timeout from code but not from DB
- Wrong calculation in SQL-Server
- Add number + varchar to get string concatenation
- How to select only the first rows for each unique value of a column?
- Making a data access class for all programs or individual C#
- How to get image from SQL Server to C# via SqlCommand?
- Get SQL result into variable
- SQL Server EXCEPT
- How to get records where at least one of the fields has a number in SQL Server 2014?
- Adding column to pivot query in SQL Server
- How can I define a constraint in between two tables but only if the foreign key is not null?
- Selecting rows which does not have particular values against the values of another column
- Hide password field in sql server
- SQL Query stops running when sent from Access VBA
- How to Store list of data from Database to ArrayList or List in C#
- Insert into new table from a view
- Merge 2 tables and pull the latest record from them