PostgreSQL Triggers Part 1 (2022-08-24)

SQLServerCentral Newsletter for August 24, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [PostgreSQL Triggers Part 1]( - [Capturing SQL Server Deadlocks using Extended Events]( - [Running Unit and Integration Tests during Flyway Migrations]( - [From the SQL Server Central Blogs - Attach All SQL Datafiles in a Directory]( - [From the SQL Server Central Blogs - Using DBCC CLONEDATABASE to create a schema only copy of your database.]( Question of the Day - [Operator Precedence]( The Voice of the DBA  Daily Coping Tip Be kind online. Share positive and supportive comments 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. Looking Back This editorial was originally published on 30 Mar 2020. It is being republished as Steve is on holiday. Things always go wrong. Those of us that build or operate software know that we will have failures at times. These could be while applying a patch, deploying a new system, during a spike in traffic, or perhaps just a mechanical failure somewhere. Most of us fix things and move on, as there's always plenty of other work. In a few organizations in which I've worked, whenever we had a large issue, we had a retrospective. I think we defined large issue as a VP or CTO become involved, but in any case, we sometimes had to have a retrospective meeting on why things went wrong. Usually these weren't [blameless](, or [psychologically safe](. They didn't end up being good meetings, nor did they serve to prevent future problems. In today's software world where we want to adopt DevOps and build better software, we need retrospectives, both when problems occur, but also periodically as we finish major portions of work. We want to learn and become better at building software, so it pays to spend some time actually assessing how we work as a team. I ran across [a post from Thoughtworks]( that talks a bit about how to make these better. I don't completely agree with the post, and I certainly think that a retrospective won't solve all the issues we've had. Nor should it. I really see the growth and adaptation of a software team as taking time. When we find lots of problems, the key to making the software development process better is to decide on something to change and try it. I'm all for an evolutionary approach for a couple reasons. The first is that change is hard and disruptive. If we can change just one or two things, then most of our work continues forward. I don't want a new set of coding techniques and a new build process at once. The second thing is that any change we make might have other consequences. We see this constantly in the world. We try to alter one thing and other parts of our process change. If we change too much at once, we might introduce lots of other problems. Let's grow and change in small ways, but do so on a regular basis. This is both for the way we work as a team and get our software to customers, as well as the techniques we use to write code. Let's code better, refactor older code when we can, and learn to improve our skills and techniques each month. We'll have a healthier code base over the long term and a healthier team as well. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [PostgreSQL Triggers Part 1]( Shivayan Mukherjee from SQLServerCentral Get a basic introduction to triggers in PostgreSQL. [External Article]( [Capturing SQL Server Deadlocks using Extended Events]( Additional Articles from MSSQLTips.com In this tip we look at how to capture deadlocks that occur in SQL Server and the associated information to help troubleshoot. We look at how to do this using the GUI and T-SQL code. [External Article]( [Running Unit and Integration Tests during Flyway Migrations]( Additional Articles from Redgate In this article I'll give a practical example of developing a database, with Flyway, in such a way that it is automatically tested with whatever unit tests and integration tests you specify whenever you migrate the branch you're working on to the next version. If a test fails, you can work out why, undo the migration and then try again. From the SQL Server Central Blogs - [Attach All SQL Datafiles in a Directory]( david.fowler 42596 from SQL Undercover Have you ever had the need to attach a large number of database in one go? There’s no way to attach multiple databases in SSMS or via script, so... [Blog Post]( From the SQL Server Central Blogs - [Using DBCC CLONEDATABASE to create a schema only copy of your database.]( Kenneth.Fisher from SQLStudies DBCC CLONEDATABASE is one of those features that I don’t use often but it’s really cool when I need it. ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  Operator Precedence What is the proper order of precedence (high to low) for these operators? 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) Using CONTINUE When do you use CONTINUE in T-SQL? Answer: When you want to skip the rest of a WHILE loop and restart the entire loop Explanation: This command restarts a WHILE loop and ignores any other statement after it inside the loop. Ref: CONTINUE - [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 2017 - Administration [SSAS Views]( - Hi, When I connected to SSAS in Management studio, I can able to see 2 databases there. The each database has one cube which is same name as the database name. When I selected datasource connection string for one cube it is pointing to the local connection. When I connect to the SQL Database Engine […] SQL Server 2017 - Development [Turning Col into Multiple Rows]( - Hello, I am interested in turning one column into multiple rows depending on the value. I tried a pivot but seem to be doing something wrong. Here is some sample code with desired results following:  -- DROP TABLE #t CREATE TABLE #t (ID int IDENTITY(1,1), CoID int, CoName varchar(100), Class varchar(100)) INSERT INTO #t […] [Select * (-----)]( - Hi, I have a problem like below one. Give, for each train station, the number of distinct (other) train stations that can be reached directly (so without using multiple trips) within one hour of travelling (i.e. the time difference between departure in one station and arrival in the other is 60 minutes or less) and […] SQL Server 2016 - Administration [TDE master key password change]( - Hello I have a requirement to change the TDE master key password in one of the environments. Do I need to redo the whole process (encrypt the dbs with the new master key password) again after I regenerate the master key password?  Kindly advice SQL Server 2016 - Development and T-SQL [calculation for year month and quarter with date dimension table]( - i want to know how i can use the date dimension table. I am trying to sum all my sales for month, quarter and Year with joining a date dimension table but not sure how to go about that can you assist or direct me to a good resource? I have a query but i […] [Query help]( - Here is the DDL of the table: I need single row per invoicenumber and line and rest of the amount and comments should to go to additional columns in the same row as shown (in the spreadsheet). What would be the best way to accomplish this? DROP TABLE IF EXISTS #temp1 CREATE TABLE #temp1 (Program […] Administration - SQL Server 2014 [Get ID from Analysis Server cube.]( - I am fixing an issue with our automated Analysis Server backups. We use SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS to get the catalog name, and then plug that into our t-sql to do a backup. The script puts the catalog name into the DatabaseID XML for the backup. The issue is, for some of the cubes, the […] Development - SQL Server 2014 [Cancel query and transactions]( - I open a new query window in SSMS and execute: EXEC stored procedure. The stored procedure contains BEGIN TRAN and COMMIT / ROLLBACK TRAN. I hit the cancel button and get told it has been done, almost instantaneously. When I then close the query window, I get the message: "There are uncommitted transactions. Do you […] SQL Server 2019 - Administration [Backup probleme with SQl Server Writer - folder offline]( - Hi, I use a tool to backup mysq MSSQL database with Writer SQL SERVER VSS. A database for a database not work because it's excluded from VSS Writer... Here the log of SQL Server Writer : [08/17/2022 16:21:58, TID 17d8] Skipping database SYNCHRONIZOR on instance SRV-SQLSERVER because file C:\NTNX\ERA_DATABASES\SYNCHRONIZOR\DATA1\data\SYNCHRONIZOR is offline. [08/17/2022 16:21:58, TID 17d8] […] [Database and logs partitions filling like crazy]( - Hello, I've got some strange behaviour lately with my sql server. Actualy there are 2 servers with all the DBs in an AOAG. Both partitions (one for DBs and one for LOGs) are showing up as almost filled, but when I check the actual files on the disk the size of the files is quite […] SQL Server 2019 - Development [Select statement results]( - Hi, I am trying to get result when only C drive is less than 10% and other drives less than 20% but exclude server vc5 for e,g,h,i drives - "SERVER='VC5' and drive not in ('e','g','h','i')".  drop table tbl_test go create table tbl_test (Server varchar(200),DRIVE varchar(200),FREE_SPACE_IN_MB int, used_SPACE_IN_MBint, Total_SPACE_IN_MBint, Precentage_free int ) insert into tbl_test […] [Cannot set a value when initializing a variable]( - The strangest thing happened. I want to make a change in a stored procedure that I have used for ages. All of a sudden I get errors when trying to alter the sp. It turns out that the culprit is the following line: DECLARE @PermissiesViaGroep bit = 0 When I change it to the code […] Reporting Services [Image within a Rectangle]( - I am being asked to overlay text objects to a government form for a client. Unfortunately, I am having massive issues with the image not re-sizing to fit the standard 8x11 paper size, it's way too big. Has anyone had such luck with resizing an image down or has code to help with this? Any […] General [Introduction about me]( - Hello all, I'm a bachelor's Student. Now, I've decided to put my articles on my own website. At my university, I'm already working on php. But I think I'll use WordPress to make my site because it's easy. So, I got to work on it and set up SQL server, but I ran into a […] Integration Services [Error when running SSIS package after deploying]( - Hi everyone, So I have designed a SSIS package in which I am using Zappysys Excel Destination: I am using this to transfer my SQL table data in to excel file. When I run this SSIS package from Visual Studio, it runs fine and give me the excel files I need. But when I am […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -