Welcome to Appsloveworld Technologies, In this session, we learn about instead of update triggers before continuing with the session, I strongly recommend below post this series.
- DML triggers with examples
- After Update Trigger in Sql Server With Example
- Instead Of Triggers in Sql server With Example
In the SQL server, there are three types of triggers DML, DDL, and Log-On. DML Triggers fired automatically in response to the DML Events examples of DML events 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.
Now we have spoken about instead of insert trigger in the previous post, in this session we learn about instead of update trigger.
Let’s understand that with an example. We have two tables here on the TblSpecialization table and the TblDoctors table.
Instead of update trigger with Example
Now, Look at below view, this view is based on these two tables. So if you look at this view, the ID, DoctorName, and gender columns are coming from the TblDoctors table and the Specialization column is coming from the TblSpecialization table.
Create view [dbo].[vWDoctorsInformation]
as
Select TblDoctors.Id,DoctorName, Gender, Specialization
from TblDoctors
join TblSpecialization
on TblDoctors.SpecializationId = TblSpecialization.Id
We have learned about views and joins in the previous post. So please check those post if you are new to views and join.
So obviously vWDoctorsInformation view is based on multiple base tables.
In the previous session, we have seen that, if a view is based on multiple tables, and if you try to insert a row into the view, it doesn’t allow you to do that.
it throws an error stating the view is not updatable because the modification affects multiple base tables. we have overcome that by creating an instead of insert trigger.
Now, let’s try to update this view. Instead of inserting a row into this view, let’s try to update this view in such a way that the update is going to affect multiple tables.
if you look at the update statement we have here, so we are updating the view and we are changing the DoctorName column and the Specialization column.
So obviously DoctorName is coming from TblDoctors and Specialization is coming from TblSpecialization tables. So now the update statement is affecting multiple base tables.
Update vWDoctorsInformation
set DoctorName = 'Henry New', Specialization = 'Cardiology (Heart Care)'
where Id = 8
We are changing the DoctorName column and a Specialization column where ID is equal to 8.
So for the 8th record, we are changing the DoctorName from Henry to Henry New and
Specialization name from Cardiology (Heart Care) to Oncology (Cancer Care).
So this updated statement is affecting two base tables, TblDoctors and TblSpecialization. So we should get an error.
View or function ‘vWDoctorsInformation’ is not updatable because the modification affects multiple base tables.”
Now to overcome this ,We can actually create an instead of an update trigger because this is an update statement. So in this view, we can create instead of update Trigger.
But then before we do that, let’s look at another example.
So we have exactly the same two tables, TblDoctors and TblSpecialization, and the same view. Now, the only difference here is the update statement.
So if you look at the update statement here, we are updating the view and we are only changing one column that is the Specialization column.
Update vWDoctorsInformation
set Specialization = 'Oncology (Cancer Care)'
where Id = 8
And we know that the Specialization column is coming from TblSpecialization. So this update statement is affecting only one base, it’s not affecting multiple base tables.
it should succeed. but then when you actually execute this statement, it updates the Specialization name in the TblSpecialization table.
So you are asking to change the Specialization to Oncology (Cancer Care) where Id is equal to 8.
But before the issue, an update statement, look at Brandon and Dr. Jacob records they also belong to Cardiology (Heart Care).
Now, when we issued an update statement, what’s our intention?
Our intention is basically to change Henry’s Specialization from Cardiology (Heart Care) to Oncology (Cancer Care).
So if you update a view that is based on multiple tables, there are two things that can happen.
If your update statement affects multiple base tables, then an error will be thrown and the statement will be terminated.
But whereas if your update works, I mean, if you update only affects one table, it may work correctly, like if you just update the name and gender it will correctly.
But if you update the Specialization name, then it may incorrectly update, the update might happen, in the wrong way. And to overcome that, we can make use of instead of update triggers.
as you can see in the above is if issue an above update statement, Brandon & Dr. Jacob records are also updated.
Let’s see how to create and instead of update triggers to correct the situation.
Create Trigger tr_vWDoctorsInformation_InsteadOfUpdate
on vWDoctorsInformation
instead of update
as
Begin
-- if TblDoctors is updated
if(Update(Id))
Begin
Raiserror('Id cannot be changed', 16, 1)
Return
End
-- If Doctors Specialization is updated
if(Update(Specialization))
Begin
Declare @SpecializationId int
Select @SpecializationId = TblSpecialization.Id
from TblSpecialization
join inserted
on inserted.Specialization = TblSpecialization.Specialization
if(@SpecializationId is NULL )
Begin
Raiserror('Invalid Specialization Name', 16, 1)
Return
End
Update TblDoctors set SpecializationId = @SpecializationId
from inserted
join TblDoctors
on TblDoctors.Id = inserted.Id
End
-- If Doctors gender is updated
if(Update(Gender))
Begin
Update TblDoctors set Gender = inserted.Gender
from inserted
join TblDoctors
on TblDoctors.Id = inserted.id
End
-- If Doctors Name is updated
if(Update(DoctorName))
Begin
Update TblDoctors set DoctorName = inserted.DoctorName
from inserted
join TblDoctors
on TblDoctors.Id = inserted.id
End
End
Now if you issue a update statement it will update the data correctly
Update vWDoctorsInformation
set Specialization = 'Oncology (Cancer Care)'
where Id = 8
select * from [vWDoctorsInformation]
Explanation of above instead of update triggers Query
It’s just that a bit of copy-pasted code if you understand one section, is pretty much similar.
so we know that triggers make use of two special tables called inserted and deleted. And we have spoken about them, in the previous three post.
The inserted table contains the new data that you have inserted, whereas the deleted table will contain the ROWS that you have deleted.
But whereas when you updated the view or a table .inserted table will contain the updated new data, whereas the deleted table will contain the old data before updations.
So we’ll make use of those tables. So obviously when somebody issues an update statement. Then we have inserted table and deleted table which will have the old and new values.
we are creating a trigger, Create Trigger trigger_name on vWDoctorsInformation view instead of update. So the trigger gets fired instead of the actual update statement.
update(Id) function will return false and we wouldn’t get into below block. But whereas if you check it, I mean if you look at Update(Specialization) condition, it’s checking update Specialization.
Basically you can use the update function to determine if the user is actually updating that column.
So if you look at the view has got, Id DoctorName, gender and Specialization columns. So we have to check if each column has been changed.
And to do that, you can actually make use of the update() function. update() function checks if the column has been changed by the update statement.
So you need to check. if I Id column has been updated, the TblDoctors ID is updated, then we want to throw an error because you cannot change a primary key.
we are using the Raiserror() function. If you want to through a custom error, then you can make use of the Raiserror() function. So we are throwing in an error message saying Id cannot be changed and the severity level and state.
Next, we are checking if Specialization has changed. So if Specialization has changed. so if the Specialization is changed, then our intention is basically to update the SpecializationId column, not to change the Specialization name column in the TblSpecialization table.
So to do that, obviously, you will have to get the SpecializationId that is associated with this Specialization name. And to do that, you can actually join the newly entered Specialization name with the TblSpecialization table and where the newly updated department name will be present.
It will be present in the inserted table. So you’ll have to join the inserted table with the TblSpecialization table and then get the SpecializationId column from the TblSpecialization table.
So that’s what we are essentially doing. we have this variable @SpecializationId which stores the SpecializationId from TblSpecialization
Then you need to check, is there a SpecializationId is NULL. if SpecializationId is Null , means the user has typed in some garbage value.
Obviously if I type in the garbage, obviously there is no Specialization with that name. So I don’t get any SpecializationId back.
So @SpecializationId will be NULL then we want to throw an error saying that invalid Specialization name and then return back. You don’t want to process anymore.
The post Instead Of Update 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
- SqlParameter DbType.Date -> SqlDbType.DateTime Conversion
- EF Code First Calling Stored Procedures with Complex Return Type
- SQL Server : drop schema with special characters
- Delete all rows except 100 most recent ones
- Right click script alter table disabled in SQL Server Management Studio
- SQL - Pivot table from a multilayer SELECT statement
- How can I convert a float into int using the 'round' method in SQL Server?
- nvarchar and varchar were showing same data
- Indexing in SQLServer
- JDBC Broken Pipe Mac
- TSQL 2012 Format String Literal with AM and PM
- How to read encrypted database field using Hibernate
- Generic EF6 Repository Method Doesn't Generate Correct SQL
- Windows Server: Number of simultaneous connections for IIS and SQL Server
- Safe DateTime in a T-SQL INSERT statement
- issue on database query to fetch data with subquery
- SQL Multiplying DECIMAL(18,4) by DECIMAL(3,2) return DECIMAL(18,4)
- EF Core 3.1 creates duplicate column with '1' in name when defining Principal Key relationship between entities
- SQL Server - How calculate # of entities to hit 80% of sum total?
- Get the max value of a column from set of rows
- Group repeated rows in TSQL
- SSIS : Conversion text stream DT_TEXT to DT_WSTR
- Organize stored procedures, views, functions etc with sql management studio
- No column name was specified for column 2 of 'a' error
- Asp.Net Core + SQL Server on Docker - sleep for startup DB
- How to compare a datetime without removing the time IN SQL Server
- Change ID to same ID if other value is same
- If I Query a SQL Server VarBinary(MAX) Column, Will the Entire File be Loaded In Memory?
- SQL Server datetime literal - Error converting data type varchar to float
- Decimal with high precision and scale creating 'Arithmetic Overflow Error'
- ASP.NET Web Application Administration Security section: Unable to connect to SQL Server DB
- Count with union all
- How to Collapse All Regions in Azure Data Studio
- SQL How to count the amount of same occurrences for every day
- Transactional Replication Questions
- How do I get Dapper to ignore invalid data?
- Combining two tables as a view in SQL Server
- SQL Server query order by column containing string
- SQL Top clause with order by clause
- SQL Stored Procedure to get Date and Time
- Client / Server Security in C# / SQL Server
- SSIS - Teradata Attunity Connector and SQLServer 2016
- Import datatype DECIMAL from CSV to SQL server
- How to use EXCEPT clause in multiple databases
- SQL Server: link DB2 table
- In-Memory SQL/NoSQL - Keep the ability to query - Maybe use MySQL or SQL Server
- Select every nth row in SQL
- Using Recursive CTE with GroupBy
- How do I convert the following code to a SQL Server/T-SQL CTE?
- Advantages for Guid Field over nvarchar contains Guid?
- How to check if a column value is in a string SQL
- Need to write a query in SQL Server
- Choosing the right data from a table using SQL
- Remove double quotes from fts keywords?
- Linked server vs integration
- how to find percentage or area of overlapped area of a polygon
- How can calculate transaction data
- How to return top 100 rows by a column value and then randomize theese top 100 rows?
- Sorting a varchar column in customized way
- Why different variable scope between parameter and input dataset variables?
- IF UPDATE() in SQL server trigger
- SQL Find Age using DateDiff
- How to generate an integer unique id in a multi-user application without duplication in C#/SqlServer?
- Alter column to be identity
- How to use @@ROWCOUNT in IF statement as well as within BEGIN..END block?
- WPF SQL Distributed Data Model with LINQ and SQL Express
- When to qualify table or view name with dbo in a query?
- Combining SQL Server Queries
- T-SQL Convert DateTime and Count entries for each date
- SQL - Join on changing dates
- SQL Server : IF statement in WHERE clause
- Subselect in SQL Server
- Auto Increment userdefined id
- Sql table with default value will not save when passed a NULL
- TClientDataSet read Binary field to TStream
- SQL Server - select pairs of available stock options
- Execute Stored Procedure on multiple databases on multiple servers SQL Server
- Possible reasons for error: "The cast to value type 'Decimal' failed because the materialized value is null"
- SQL Update SET sub query?
- ADO.net SqlTransaction improves performance
- Unique constraint on two fields, and their opposite
- Package running in SSDT but not through the Execute Package Utility
- Performance concern for a generic mechanism of saving files
- Microsoft sql stored procedure not executing select statement properly
- Dynamic SQL (passing table name as parameter)
- SQL Server XML Joining using Exist() for Performance
- Calculating a difference between two columns within an UNPIVOT
- Android studio how to access server files?
- Create a scheduled job using MSSQL Agent
- Using Entity framework in conjunction with Task Parallel Library
- How to escape ampersand in MS SQL
- How to compose this SQL Server query?
- Is there a delay for the "INSERTED" recording binding in SQL Server 2008 R2?
- SQL Report Builder Same Key has already been added; Update with Cte
- Is there any ThreadStatic attribute for parent and child threads
- Ensure data integrity in SQL Server
- Database schema for hierarchical groups
- How to Insert data from CSV file to SQL Server with specific columns?
- Execute a SQL query/statement conditionally based on table data
- I am losing precision when converting from float to varchar
- SQL Server proc running 5x slower than plain query
- sqlsrv_connect: Data source name not found and no default driver specified
- Precision nightmare in Java and SQL Server
- how can I specify a "literal" table succinctly in a CTE?
- How to give ADO.NET Parameters
- Upload the CSV file along with the data with Headers as column name
- SQL Group By on created column?
- Inserting values into foreign Key MySQLCommand C#
- Explain this SQL query in plain English
- Normalize SQL table
- PHP sqlsrv_connect to SQL Server: A network-related or instance-specific error has occurred while establishing a connection to SQL Server
- SELECT WITH LEFT JOIN AND WHERE CLAUSE
- How to split dataset into multiple subsets and export them to Excel
- The object name contains more than the maximum number of prefixes. The maximum is 2 while running the linked query
- Type Conversion changes between Local PC and Server environment
- MS SQL Server - How to create a view using hierarchical query
- How to connect MSSQL Server 2008 with Django
- DELETE specific rows that matches all values
- Dynamically store user data
- Include SQL Server database in application
- Populating asp.net table from data from database
- How can you absolutely position each row in a dataset in a SSRS report?
- How to do a SQL count only up to x number?
- SQL Server - aggregate if only one distinct value + nulls without ansi warnings
- Can I use LIMIT N,M on SQL Server database
- Invalid Object Name - Stored Procedure
- How to sync only certain rows in a database
- SQL Key Value Pair Query
- String replace in SQL Server 2008. I want to rename "Ä?" to "c"
- How do I output a table to a txt file using tsql?
- Retrieve data types of the selected columns
- unable to assign updated value to ssis variable using script task
- MS SQL Server Actual CPU Cost per physical operation
- How to perform a dynamic pivot in T-SQL on a subset of columns
- Get total values of a parent and its child records
- Creating a SQL Server database - but I get an error
- Can you create a table with indexes at the same time?
- SQL query won't work when used from a stored procedure
- How to SELECT the last distinct record in SQL SERVER
- Generate connection table from table with duplicates
- SQL server, how to get a number of distinct items
- Why do I have different result set between SELECT * and SELECT Column1?
- SQL join with different conditions
- How can I get the Maximum of a column value in a SQL Server Stored Procedure?
- How to delete a sequence of characters from an SQL Server table
- Developer: how to get reporting data from about 20 databases?
- SQL Server 2008 : SELECT min(date)
- SQL table truncates when using a trigger to join. Require work around
- Find the last entry for specific user id
- What is the better way to select from table with two condition from the same column?