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
- Convert float (YYYY) to datetime
- Root cause of an "Invalid object name: dbo.etc" error?
- Permissions error creating .bak back-up of SQL Server database on Amazon RDS
- Set Static Day and Previous Month GETDATE() SQL
- Dynamic Date Range in SSRS or SQL Data Tools builder
- Exporting data from MS Access to MS SQL with schema and table changes
- What is the best way to compare 2 variants of a SQL query for performance?
- SQL query with datetime value shows different result
- SQL UPDATE: Subquery returned more than 1 value
- Converting YYYYMM format to YYYY-MM-DD in SQL Server
- Store deleted rows in a table
- Why use AppFabric when denormalized SQL Server data seems to perform as well?
- SQL to retrieve tree structure nicely
- Executing multiple queries in SSMS
- SSIS SQL Server Agent Schedule
- Set the constraint to start with 0 then 9 only and contain 11 digits?
- Can using isnull in a where statement cause problems with using indexes?
- How to get column-wise constraints in SQL Server
- Join table based on column value in SQL Server
- Set all the 0 values to NULL
- tbl with in_schema returns "Invalid object name" error
- How to attach database from winforms using C#
- Splitting row into row per month and splitting number of days
- Shaping dataset in MySQL with a multiple inner join
- Find a row after another row
- Need output for following with Comparison of Current row and previous row
- Log Chains and SQL Server Transaction Log - sanity check
- getting error Could not find stored procedure 'sp_configure'. in sql server
- Remove a registry if any column has a NULL value in SQL Server 2008
- Grouping by partial value
- How to fix 'Cannot load dynamically generated serialization assembly' error
- publish Database project to Azure
- SQL query to find missing values in tables
- Hibernate join Entities on a column of type varbinary?
- Set Limit for a Table Rows In SQL
- use c# to copy table data from oracle to SQL Server?
- CROSS APPLY function in Presto/AWS
- Update table in access by joining a table from sql server
- Create SQL user-defined function in ColdFusion with MS SQL Server
- Specified cast is not valid for sql server query
- Data Replication - SQL Server 2008
- Import Email from AD Mailbox
- Concatenating xml values when selecting by XQuery in T-SQL
- Need to calculate the sum of second using a function from a string variable using SQL
- Database Connection Error "file in use"
- UPDATE with isNull
- Updating SQL database using VB.net
- SQL - finding Sequence / Path
- Why does a number imported to SQL Server from Excel contain the letter e?
- Truncate table with IF condition
- Is this way to execute procedure is safe from SQL Injection?
- Msg 547, Level 16, State 0, Line 1 (help please)
- Inserting XML in table
- How to set datetimeoffset values in JPQL?
- How can I use SQL Server bulk insert in asp.net?
- How to find difference between two columns data?
- SQL for Oracle to check if a constraint exists
- Using user defined function (UDF) in where clause more than once with running the function only once
- SQL inserted - deleted Tables
- How to Create Script for hierarchyid datatype for below data?
- Stored Procedure returns schema version change error when run from SSIS, but not when run directly
- Custom Group By and place some records in one group
- Jpa Conversion failed string to uniqueidentifier
- linq to entities simple query gives "object reference not set to an instance of an object"
- How to convert mssql script to mysql
- T-sql: ranking groups by changed positive values
- SQL Server TRY CATCH FINALLY
- SQL Batch Data, Hope to find running time for each Status (minutes) based on Last Update Timestamps?
- Can SQL Server look ahead into XML tags and/or count them?
- SQL select COUNT issue
- How to update or remove all special characters from a varchar column
- Doing a join only if count is greater than one
- How To Pivot table using SQL server
- Getting the value of a selected item within a List box connected to a Sql Table
- Using db names in dynamic SQL select statement
- Incompatible Database Versions when syncing
- "Create new table" is not visible on SSMS 2016 for SQL Server 2008
- Select from sql table, fill in non-existing entries
- SQL Server : how to cross join table
- Convert String to date in select statement
- Divide selected value by count(*)
- can fuzzy lookup and fuzzy grouping operations be performed in azure data factory
- update join causes error: Subquery returned more than 1 value
- Sql does not queue queries to linked server
- Reporting Services: Use procedure as dataSet?
- how to connect asp.net core MVC project to local DB using Entity Framework Core on ubuntu desktop
- Defensive database programming- robust code with T-SQL?
- Is it possible to enforce database integrity based indirect relationship
- Check Constraint Expression error
- ConnectString didn't work in C#
- ORDER BY AND CASE IN SQL SERVER
- How to get the xml-safe version of an sql server XML Column
- SQL query that applies aggregate function over another aggregate function
- Where Clause Index Scan - Index Seek
- Updating row of table Using data from multiple columns of another table
- What happens to CPU when WAITFOR statement is executed in SQL Server?
- How to update an XML column with value from T-SQL built-in function?
- ASP.Net Nested Repeater Logic Hell
- Strange behaviour of CASE construction
- How to create dynamic database connection string C#
- Edit Column in SELECT Statement on Navicat SQL Server
- Filter SQL Query based on Last Values in the resultset
- Return Word in String - SQL
- SQL query to find missing data between 2 tables with groups
- Simplify search for matching values
- Why won't this SQL CAST work?
- How does one summarise data grouped by a column and date, accounting for dates with missing data
- Which database stores sys.tables or sys.indexes in SQL Server?
- Connect android app to SQL Server
- How to create a surrogate key column in existing table?
- Is this an acceptable database design?
- Unicode characters causing issues in SQL Server 2005 string comparison
- sQL: export database to asp.net
- Should I drop and re-create database to save space?
- SQL - How to return entire row sets where some rows match a given list
- I'm having trouble understanding this example of the EXISTS clause
- Find address by latitude and longitude from a point (lat, lng) given
- Visual Studio 2017 not showing SSIS
- Check if one of many temporary table exists and have values in SQL Server 2014
- Is there any SQL Server studio managment and redgate sqlcompare/datacompare like tools for MySQL?
- TSQL: WITH cteTABLE insert into other_table
- Insert highest absolut value filtered by category into another table
- Can Access pass-through queries see global temp tables on SQL Server created using ADO and/or SSMS?
- Which is better, Using multiple column in GROUP BY or GROUP BY with AGGREGATE?
- List all MSSQL servers in the network VB.NET
- TSQL Random Select with Selective Criteria
- show manager approve documents tHRough store procedure
- REGEX replace in T-SQL
- Find 2nd last record(if available otherwise the last record) in sql server
- Is there a SQL Server 2008 method to group rows in a table so as to behave as a nested table?
- Converting XML to SQL Server Table using C# (or any other method)
- Run my WinForms Database Application in another Computer
- Reporting Services Report Builder, get a specific number and add to all other lines
- SQL Query on single table-valued parameter slow on large input
- How to delete only recode from table?
- How can I specify the path to a file dynamically in OPENROWSET(BULK...)?
- Full-text search on in-memory optimize table sql server
- Filter child elements, but keep the original structure with XPath
- When are database triggers bad?
- adding a value to a column from data in next row sql
- CASE in SELECT WHERE statement for stored procedure?
- Can this business rule be enforced with an index?
- Creating a Symmetric AES128 Key in SQL Server based on Key and Initial Vector
- Post Build in SSIS Project
- How to select query that one id is on the top and rest of them normal ordered
- Sum rows with no field to group by
- Insert Double In Sql Server
- Executing SQL code
- SQL Server Database real-time replication
- Query XML data in SQL Server 2008 R2