Hello, welcome to Appsloveworld Technologies, In the session, we learn about instead of triggers specifically we’ll be talking about instead of insert trigger before continuing with the session, I strongly recommend the below post of triggers series.
In SQL over, There are three types of triggers DML, DDL, and logon triggers. DML triggers are fired automatically in response to the DDL events. Examples of DMLevents include insert update and delete
DML triggers can be further classified into two types after triggers and instead of triggers. After triggers are fired after the triggering action whereas instead of triggers are fired instead of the triggering action, the name itself suggests that.
Instead of insert trigger
So in this session, we’ll be talking about instead of triggers, instead of insert trigger specifically, let’s try to understand that with an example.
I have two tables here, TblDoctors which has got Id name Gender and SpecializationId Columns, and TblSpecialization we just got SpecializationId and name columns.
Now let’s say I want to create a view based on these two tables which return data like the below image.
So I want the ID, name, and Gender from the TblDoctors table and Specialization name from the TblSpecialization table.
To create this view we have to join these two tables. So this view is based on multiple tables. So this view has got multiple base tables.
Create view vWDoctorsDetails
as
Select Id, DoctorName, Gender, Name
from TblDoctors
join TblSpecialization
on TblDoctors.SpecializationId = TblSpecialization.SpecializationId
Now let’s say when I try to insert a record into this view, and if you look at the view itself, it has got one, two, three, four columns, DoctorName, gender, and Specialization name.
Insert into vWDoctorsDetails values(7, 'Dr. Tanmay', 'Male', 'Neurology')
we know that a view is a virtual table, meaning it is nothing more than a stored SQL query. It doesn’t really contain any data.
The View actually gets data from its underlying base tables, in this case from TblDoctors and TblSpecialization. So when we try to insert a row into this view, behind the scenes, insert that row into these base tables.
Now when we execute the above insert statement, the SQL server has a confusion. which column should go into which base table?
That’s why if your insert statement is affecting multiple base tables, then SQL Server will through an error stating so.
Msg 4405, Level 16, State 1, Line 15
View or function ‘vWDoctorsDetails’ is not updatable because the modification affects multiple base tables.
Now let us see how to correct the situation using instead of triggers. Now instead of triggers are usually used to update views correctly that is based on multiple tables.
so since this view is based on multiple tables and we are trying to insert data into this view, let us see how we can make use of instead of insert trigger to correctly insert the row into the underlying base table.
INSTEAD OF INSERT Trigger Example:
Create trigger tr_vWDoctorsDetails_InsteadOfInsert
on vWDoctorsDetails
Instead Of Insert
as
Begin
Declare @SpecializationId int
--Check if there is a valid SpecializationId
--for the given Specialization
Select @SpecializationId = SpecializationId
from TblSpecialization
join inserted
on inserted.Name = TblSpecialization.Name
--If SpecializationId is null throw an error
--and stop processing
if(@SpecializationId is null)
Begin
Raiserror('Invalid Specialization Name. Statement terminated', 16, 1)
return
End
--Finally insert into tblEmployee table
Insert into TblDoctors(Id, DoctorName, Gender, SpecializationId)
Select Id, DoctorName, Gender, @SpecializationId
from inserted
End
So obviously we need to create a instead of insert trigger, so create a trigger and give a meaningful name to the trigger triggers usually have tr Prefix so tr_ the object on which we are creating the view.
Here we are creating the view on vWDoctorsDetails, underscore for which action you are creating this trigger. We are creating this trigger instead of insert action.
Now, let’s execute the insert query:
Insert into vWDoctorsDetails values(7, 'Dr. Tanmay', 'Male', 'Neurology')
New row inserted into the base table
Explanation of Above Trigger
So if you look at the implementation of the trigger, so we are creating a trigger on the view, Instead Of Insert as Begin.
Then we are creating a variable to hold the specializationId. So which is of type integer and we are selecting the SpecializationId from TblSpecialization table joining that with the inserted table. And we have spoken about joins in great detail in the view series.So please check them if you are new to Joins.
So we are joining that with inserted a table on the Specialization name. So once you have the SpecializationId, what you need to do now is insert that into the TblDoctors table.
But before that, look at this. If somebody supplies the Specialization name in insert query as some garbage like Null or Invalid Name.
Like below query
Insert into vWDoctorsDetails values(7, ‘Dr. Tanmay’, ‘Male’, ‘Neurology Master’)
Now in the TblSpecialization table, we don’t have a Specialization with ‘Neurology Master’ name.
So obviously this trigger gets executed, SpecializationId will be NULL because there is no Specialization with that name.
if a user is trying to insert a Invalid Specialization name, then we should throw an error and we are using Raiserror() for that.
And then there are two other parameters that we are passing to Raiserror() function.
The first one is the severity level. There are several severity levels we usually use 16,16 means that is something that user can correct and resubmit their query. And the state, which is usually one.
So if the @SpecializationId is NULL, Raiserror() error message, and then stop processing and to indicate that we use the return keyword.
Sql Table Script
GO
/****** Object: Table [dbo].[TblDoctors] Script Date: 10/13/2020 8:31:11 PM ******/
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/13/2020 8:31:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblSpecialization ](
[SpecializationId] [int] NOT NULL,
[Name] [nvarchar](max) NULL,
CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED
(
[SpecializationId] 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 (8, 'Henry', 'Male', 4)
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 ] ([SpecializationId], [Name]) VALUES (1, 'Neurology')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (2, 'Urology')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (3, 'Surgical Gastroenterology.')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (4, 'Cardiology (Heart Care)
')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (5, 'Oncology (Cancer Care)
')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (6, 'Bone Marrow Transplant.
')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (7, 'Medical Gastroenterology.
')
GO
The post Instead Of Triggers 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
- How to run a create index script using RoundhousE that depends on a sp in the correct sequence
- ms sql temporal table find history on column where last changed
- How to edit HeaderText for TemplateColumn inside DataGrid
- T-SQL select query with generate JSON result group by one column in SQL server 2008
- What does the SQL # symbol mean and how is it used?
- How to Install wordpress with SQL server
- SQL, moving million records from a database to other database
- SQL SERVER : Date Format DDMMYYYY
- How to declare Array variable in SQL Server?
- how to call a FoxPro stored procedure from T-SQL Script
- Get records ordered alphabetically starting by a certain letter in Sql Server
- How to get the last month's data for a quarter in SQL Server?
- asp.net mvc Invalid value for key 'attachdbfilename'
- Selecting first letter of each word in a string in SQL
- How can I configure Sql Server Management Studio to ignore certain extended properties on a view when creating a BACPAC?
- dynamic pivot for resultset
- Is there a way to test for a specific sql exception type in C#?
- Store filename of uploaded file in database java
- reporting services show/hide column base on dynamic query
- Extremely slow insert OpenQuery performance on SQL Server to MySQL linked server
- Selecting records from slowly changing table with a set of dates
- Assign value randomly but within distributions within grouping
- Is there any Ruby framework to return database data to XML?
- Analysis Services with excel as front end - is it possible to get the nicer UI that powerpivot provides
- How to create value to be inserted based on identity in SQL Server?
- Using SSDT, how do I resolve SQL71561 errors when I have a view that references objects in a different database?
- How to group orders by Date
- Loading CSV into SQL Server error
- I want to insert values for Ranks column on bases of other column required condition
- SQL and Case Insensitive Conditions
- NHibernate mapping a reference table
- C# database connection error on remote server
- insert rows and increase a value in a field
- Sorting varying sizes of blank strings in SQL Server
- SQL Server round()
- Getting a SQL Query to work in Access
- Sql server select - varchar containing column name and value
- PHP & MS SQL: sp_helpconstraint <tablename> will not allow me to advance to the second data set with mssql_next_result()...?
- I want the last entries in my Microsoft Database (MS SQL) but get duplication
- Calling stored procedures in Laravel 5.5 using SQL Server with output
- SQL Server Pivot/Unpivot an entire table (100+ fields)
- How to create a polygon from a point?
- How to prevent arithmetic overflow error when using SUM on INT column while inserting?
- Entity framework and Stored procedure
- Code a button to open form, based on what is selected in list box (list box selected index is from a SQL Server)
- How to delete only rows that comply with condition
- "IN" keyword sql file path
- SQL Server string search and evaluate
- Filtering unicode column in Sql server 2005 - to much data match
- how to eliminate unneeded record
- How to migrate schema from SQL Server 2008 to SQL Server CE 4?
- SqlExceptionHelper: Cursors are not supported on a table which has a clustered columnstore index
- Can __int64 be stored in sql server Int if value is within __int32 bounds?
- SQL Server 2008 automatically creates table when creating new database
- days of the month
- How do I Switch single quotes to double single quotes on output from T-SQL SELECT statement
- SQL: count emails per year
- SQL Server - PIVOT using variable
- in sql server, what is: Latin1_General_CI_AI versus Latin1_General_CI_AS
- Updating millions of Row after Calculation
- SSRS - sorting by a single tablix column result in a column group
- Convert from DateTime to INT
- Rolling 7 Day Distinct Count of Active Users
- Data from past 30 days works, but the previous 30 days does not?
- Editing xml using SQL
- how we delete a row from parent table when child table connected with paren table by forigen key
- Connecting Azure SQL Database, from Azure Web Site running PHP 5.5?
- CTE - recursively update quantity until total consumed
- Update an existing entry with Entity Framework
- Azure - WebAPI unable to connect to Azure DB, Error: The system cannot find the file specified [SqlException]
- Group count and Pivot Query
- Summarizing a table in SQL
- SQL Server Express cannot connect error: 28 - Server doesn't support requested protocol
- Connecting to a SQL Server database using VB.NET
- Parameter Mapping using an Execute SQL Task
- How is a varchar index stored?
- Fetching data from multiple columns of same type in SQL Server.
- Pyodbc returning (Decimal('100.00'),)
- How to connect with Pyodbc with function?
- Adding default constraint to column
- AWS DMS replication instance unable to connect local machine SQL Server test endpoint
- debugging in SSMS 18.2
- Data migration from MS SQL to PostgreSQL using SQLAlchemy
- What is the SQL Server equivalent of the following Access statement
- Edit TOP Row in SQL Server Management Studio
- Last row in SQL
- What are the differences in using driver {SQL SERVER} vs Native Client
- How to connect listbox to table c# winforms
- Possible to configure database autogrowth settings at the instance level?
- Can I work with both local and ODBC linked tables in an Access database from Python?
- SQL Server Fast Forward Cursors
- Get tablename from function
- SQL query performance is slow after table reindex but fast after EXEC sp_updatestats
- comparing two tables to make sure they are same row by row and column by column on SQl server
- Using Google Maps in PHP and SQL Server
- EF Core set XACT_ABORT for all database connections
- Error converting data type varchar to numeric using a case function
- SQL Subtruct Quantity from the MAX Calculated Value Row with values from another table
- hide a column from table in MS SQL Server
- SQL Server : query runs very slowly (up to 20 seconds)
- Generating unique logon id failing when loop reaches 9 in sql 2012
- Link one record to multiple records in separate table
- How to select records where a column is equal to a parameter that can be null in SQL Server
- SQL Server ALTER field NOT NULL takes forever
- T:SQL: select values from rows as columns
- Parallelizing massive inserts in SQL Server from C# (for better time performance)
- Using CTE with paging to fetch records from 3 tables
- SQL Server and Microsoft.ACE.OLEDB.12.0
- How to insert images into a SQL Server table
- Uploading existing database through Plesk
- Adding one day in T-Sql give error Incorrect syntax near '1'
- How do I escape a single quote in dynamic SQL
- How do I add a column derived from a select statement within a select statement?
- How to filter data into new column? sql
- Replace first FROM in sql query
- SQL Server 2014 performance - parameterized SQL vs. literals
- Java INSERT to SQL Database with JDBC
- Database structure, Users + User Types where Users can be of more than one Type
- Convert SQL dump to JSON?
- Storing operators with operands in table in SQL Server
- Multiple "ID" columns in SQL Server database?
- SQL command: get min date and hour from table
- SQL Text comparison - dealing with multiple apostrophe types
- SQL Server : after using GROUP BY with CASE, the ORDER BY doesn't work
- nvarchar(256)....?
- PIVOT two columns and keep others as they are
- Script task inside a SSIS For Each loop - writing to a file
- SQL server removing traling zero after decimal points in a string
- Cannot implicitly convert type 'string' to 'System.Web.UI.WebControls.Label'
- query that groups by different columns including time difference
- .NET not showing instance or version when enumeration is done via SqlDataSourceEnumerator.Instance.GetDataSources()
- SQL Do not return column if value is zero
- Flattening schedule data in SQL server
- Not getting actual minutes in SQL DateDiff
- Splitting string and adding them to another table
- Divide phone and extension from phone number in two columns in SQL query?
- Msg 5074, Level 16, State 1, Line 1 The index 'IX_Alias_Alias' is dependent on column 'Alias'
- How do I set a column in SQL Server to varchar(max) using ASP.net EF Codefirst Data Annotations?
- Microsoft SQL server. Getting External script execution failed as extensibility environment is not ready yet. CTP1.2 LINUX version
- Importing MSSQL database backup to MySQL workbench
- Best way to randomly select rows *per* column in SQL Server
- How to reuse column alias in another column within same select statement?
- EF Generate Database from Model Deletes Function Imports
- Not able to connect to the SQL Server when executing the command
- SQL SERVER 2008 R2 find column words in another column
- Creating a SQL Server UDDT for an IP Address?
- Must declare a scalar variable (C# and OleDb)
- T-SQL Case Condition in Where Clause
- Calling a stored procedure in MVC
- Using sqlsrv_connect on Platforms other than Windows