A Simple Tutorial for DML Triggers (2023-02-15)

SQLServerCentral Newsletter for February 15, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [A Simple Tutorial for DML Triggers]( - [Manage Data Over Time with SQL Server MERGE Statement]( - [Getting Started with Flyway Migrations on Oracle]( - [From the SQL Server Central Blogs - Why Managed Identities are Awesome]( - [From the SQL Server Central Blogs - Context Info Across Databases–#SQLNewBlogger]( - [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Question of the Day - [Storing Always Encrypted Keys]( The Voice of the DBA  Daily Coping Tip Look for good in others, particularly when you feel frustrated I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. The Cost of the Cloud Basecamp (formerly 37 Signals) is [quitting the cloud](. One of the founders gives some reasons, and he had [some detail in a tweet]( on what they've spent in the cloud the last few years. Over USD$3mm on various services, though their costs in search seem very high. I don't, and haven't, run as busy a business as they do, so I don't know if they've truly done a good job architecting things and setting up services. They say they have, though I'd expect everyone to say and think that. However, I'll assume they are correct and they can't optimize things any more than they are currently. Their decision makes some sense, and I agree with it. I've been surprised at the growth of the cloud, both in size and how quickly people are moving to the cloud. I've also been saying for years that if you have a steady or known workload, the cloud is likely very expensive. Maybe that's worth it for your organization. Not dealing with physical resources, maybe having slightly less staff, maybe less CapEx vs. OpEx. Those are decisions for management and finance people. For most of us, the cloud both simplifies some tasks and makes others more complex. Provisioning, testing out Proof-of-concepts, and scaling are easy. Identity protocols, gaining (and keeping) knowledge of how various options work (networking, storage, etc.) , and keeping track of resources become more complex. Not to mention the world constantly shifting under your feet as cloud providers change how their platforms work. There are costs in both hard dollars (or your currency of choice) and in the time your staff spends dealing with a new way of doing business. The calculation of whether this is a cost that makes sense is very dependent on your situation. I have customers that love the cloud and others that hate it. The value they get varies dramatically and some would never go back to data centers while others are ready to work long hours to leave the cloud. Overall the sentiment is the cloud is great, but like many decisions made by management there are particulars that baffle the technical staff. The one thing I have learned about the cloud is that it takes a different sort of mentality from staff than on-premises resources. We have to learn to spin things up and down, scaling as needed. We need to better understand budgets and not look at costs as though they were personal expenses. We also need to be flexible with resources, understanding that machines that are idle are not sunk costs; they are ongoing costs. The cloud is amazing, and I think it is very useful in lots of situations, but a blanket move to the cloud can be expensive. Make sure that everyone involved in moving to the cloud understands that. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [A Simple Tutorial for DML Triggers]( Vincent92 from SQLServerCentral Learn the basics of DML triggers in this article [External Article]( [Manage Data Over Time with SQL Server MERGE Statement]( Additional Articles from SimpleTalk Once data is in a table it needs to be maintained.... [External Article]( [Getting Started with Flyway Migrations on Oracle]( Additional Articles from Redgate A quick demonstration of using Flyway with Oracle, for those of a nervous disposition. We'll use Flyway to run some migrations on an Oracle Autonomous Database, building the initial version of the database then filling it with development data. [Blog Post]( From the SQL Server Central Blogs - [Why Managed Identities are Awesome]( Koen Verbeeck from Koen Verbeeck For the first workdays of this new year, I had the pleasurable task of resetting a bunch of passwords. The passwords belong to Azure AD users, service principals, SQL... [Blog Post]( From the SQL Server Central Blogs - [Context Info Across Databases–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Does Context Info work across databases? This post shows it does. Another post for me that is simple and hopefully serves as an example for people trying to get... [Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen]( [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Greg Larsen from SQLServerCentral Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Storing Always Encrypted Keys What is the storage location for the keys used in Always Encrypted? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Query Store Space Usage in SQL Server 2022 I created a new database on a SQL Server 2022 instance. This database has had a high workload for the last month and it appears that the Query Store is using well over 100MB of space to store data in my database. This was created with all defaults and other DBAs say they haven't configured anything. What is going on? Answer: The default query store space usage in SQL Server 2019+ is 1000MB, not 100MB Explanation: The Query Store defaults changed in SQL Server 2019. Space defaults to 1000MB now. Ref: - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2016 - Development and T-SQL [Plan Handle exists but NO QUERY PLAN?]( - Is this a fully legit situation in the result of the below query to have Plan Handle but NO QUERY PLAN? SELECT * FROM ( SELECT DBName = DB_NAME(ps.database_id) , ObjectName = OBJECT_NAME(ps.object_id, ps.database_id) , cached_time = ps.cached_time , last_execution_time = ps.last_execution_time , last_elapsed_time_secs = ps.last_elapsed_time / 1000000.0 , execution_count = ps.execution_count , PlanHandle = […] SQL Server 2019 - Administration [TempDB misconfiguration]( - Bit of a dilemma,  I accidentally entered the incorrect sizing of the tempdb files during the installation of a clustered instance. the installation completed but the tempdb partially created the files then ran out of space.  The issue is the SQL instal completes but failed on a few steps and now it will not start. […] SQL Server 2019 - Development [Operating System Error on Bulk Insert]( - Hello everyone, it's my first post here, and I'm no expert on SQL, so I hope you're able to help, and will forgive my ignorance, I'm keen to learn! I'm trying to import around 100 txt files into a SQL table, for processing ahead of bringing into PowerBI. I've built a query, and a layout […] [Split field to top 4]( - I need to split the column 'DiagnosisCodes' into 4 new columns 'Diagnosis1', 'Diagnosis2', 'Diagnosis3' and 'Diagnosis4' for the unique ChartProcedureId's. If they add more DiagnosisCodes, I only need the top 4. Any assistance is appreciated. SQL Server 2008 - General [Is there a way to have SQL Server be aware of a new file?]( - Every weekday we receive a file which under normal circumstances gets processed by a SQL Job that runs on a schedule at 7 AM. It's worked this way for years. This file is produced by a partner, who uploads it to a secure FTP site. However, for the last several weeks the file has either […] SQL Azure - Development [Reducing varchar(max) cols that are in auto generated indexes]( - Hi all, I inherited an app schema that is shared across 140+ different DBs [one for each client; they all have their own data but the schemas are otherwise exactly the same]. I am their tsql dev, and have been doing a lot of performance enhancements. One thing I'm running into is that one of […] General Cloud Computing Questions [Azure SQL managed Instance tiers comparison]( - Hello everyone. I have an azure general purpose managed instance, 8vCore, 1Tb storage running. The average CPU usage is 50%, with 70-75% in peak hours. Now we have a new report that need almost real-time data and since the server is heavy used, the query get a lot of concurrrency with writes tasks. Noted that […] [What are the alternatives of S3 Object Lambda?]( - Hi all! AWS has a good example of how to manipulate the requested objects from S3 and it is S3 Object Lambda which can be placed as a middleware between Amazon S3 API Consumer and S3 bucket. What are the alternatives of that in Azure, GCP, Alibaba Cloud, Oracle Cloud, RedHat, VMWare, SalesForce, DigitalOcean, etc? General [SQL Script plug-in]( - Is the SQL Script plug-in listed here no longer available? SQL Scripts in SSMS Is there any other plug-in that can be used to search SQLServerCentral scripts? Integration Services [CozyRoc REST Connection and Source]( - Any CozyRoc REST connection users out there? I'm looking for an explanation of how to set up error handling for 400 and 404 errors so that the source component won't report an error for these but just move on. I'm using some in a loop, passing an item number as a parameter and if there […] SQLServerCentral.com Website Issues [Warning prompt when writing a reply]( - When I wrote this reply and pressed submit I got an error "Are you sure you want to do that?" I could be missing the obvious but I'm not sure why the website queried my post? Possibly it's looking at language and it thought my post had a negative tone to it? Unless I'm […] SQL Server 2022 - Development [Best way to combine records across databases]( - Data which is distributed over multiple databases needs to be gathered and then queried/filtered. Currenttly a temporary table in which the records are inserted from the different databases is used however it is really slow, is there a faster way to handle it? [SQL Server Database Redunduncy]( - I am having two databases A and B Both are logging in Same DATA at the same interval of Time But in case of power failure system A it should copy missing data from system B and Vice Versa In case of power failure of System B it should copy the missing data from System […] [Return a set of rows between 2 values (multiple times)]( - Hello  I have a table, a (sample only shown) This has a set of ids in it  CREATE TABLE a (id int) INSERT INTO a (id) VALUES (1, 4, 8, 15)  I'm trying to create a set (table) that holds the id in column 1 (call it pid) and links all the […] [Update records using previous row and calculated value of current row.]( - Hi All, I am trying to update a table's column avg_gain based on previous avg_gain and current gain column for RSI calculation. I was able to implement this logic through cursor but it is time consuming, so was trying to see if there are other techniques to achieve it for having performance gains; The Formula […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -