Stairway to Database Design Level 7: Triggers (SQLServerCentral 1/24/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Stairway to Database Design Level 7: Triggers]( - [Free eBook: Inside the SQL Server Query Optimizer]( - [How to Change the Default Backup Directory]( - [Linguistics Crisis In Microsoft Technology]( (From the SQLServerCentral Blogs) - [How I designed my VMware vSAN based Home Lab]( (From the SQLServerCentral Blogs) Featured Script - [Insert data into a SQL Server Table using Powershell using Invoke-SQLc]( The Voice of the DBA Effectiveness This editorial was originally published on Nov 7, 2013. It is being rerun as Steve is on vacation. I saw a great quote recently on an internal email at Red Gate. We had deployed a new feature to a website, the application seemed to be working, but someone questioned the architecture of the implementation. The person who was in charge of the project sent this back at the end of an explanation: "It’s a little clunky, but we decided it was better to get it done than make it perfect." That's a great quote. In fact, that's what I've aimed for in most of my career. Move things forward, get them done in an effective manner. That's what I've tried to do as long as I've been in technology. Examine a problem, consider various solutions, and then choose one to move forward with. At the end of the day, as has been said by many people, "[shipping]( [is]( [a]( [feature](". This doesn't mean giving up on quality or accepting anything, but it does mean that you continue to move forward. You may make compromises on code quality, but small compromises. Your code has to still solve the problem. Your database still has to handle the load. You still need quality work, but a solution you can get done quickly that performs 10% worse may be better than a faster solution that takes twice as long to develop. Lots of code lives in systems for years. We want this code to be "good" in that it needs to work, handle the load, and contain few bugs. However lots of code also gets refactored over time. We should minimize bad code as much as possible, but less than perfect code isn't bad. Sometimes it's just a little clunky, but it gets the job done. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [SQL Compare]( The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. [Download your free trial]( [Database DevOps]( Benchmark your Database DevOps maturity level Get a better understanding of how advanced your current processes are, receive recommendations for improvements, and see how your maturity level compares with that of your peers. [Complete the Database DevOps Maturity Assessment]( Featured Contents  [] [Stairway to Database Design Level 7: Triggers]( Joe Celko In levels one to four, we built the tables, base and virtual, of a schema. Levels five and six dealt with stored procedures. This level deals with a feature you need to avoid as much as possible; this is article is on Triggers.[More »](Series/71822/) ---------------------------------------------------------------  [] [Free eBook: Inside the SQL Server Query Optimizer]( Press Release from [SQLServerCentral.com]() This free eBook from Redgate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way.[More »]( ---------------------------------------------------------------  [] [How to Change the Default Backup Directory]( When you install a SQL Server instance you have the opportunity to identify the default location for your instance database backups. Overtime you might decide you want to change the default location for database backups. This tip shows you two different ways to change the default location for your database backups.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Linguistics Crisis In Microsoft Technology]( Jeffrey Yao from [SQLServerCentral Blogs]( With technology expanding in both complexity and quantity, the task to name each component and functions of a technology product...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [How I designed my VMware vSAN based Home Lab]( Klaus Aschenbrenner from [SQLServerCentral Blogs]( (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the...[More »]( Question of the Day Today's Question (by Steve Jones): I've got this class (S4) in R. > setClass( + "NFLTeam", + representation( + name = "character", + City = "character", + conference = "character", + division = "character" + ) + ) I create an instance with this code: denver.broncos = new("NFLTeam", name="Broncos", City="Denver", conference="AFC", division="West") Now I want to get the value of the City property. How do I access this? Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: R Language. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [Exam Ref 70-761 Querying Data with Transact-SQL]( Prepare for Microsoft Exam 70-761–and help demonstrate your real-world mastery of SQL Server 2016 Transact-SQL data management, queries, and database programming. Designed for experienced IT professionals ready to advance their status, Exam Ref focuses on the critical-thinking and decision-making acumen needed for success at the MCSA level. [Get your copy from Amazon today](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I want to make the $drive parameter of my Get-DbaFreeSpace function mandatory. What declaration should I use? Answer: function Get-DbaFreeSpace {param([Parameter(Mandatory = $true)] [string] $Drive) Explanation: The [Parameter(Mandatory=$true)] block before a parameter is used to make sure a parameter is passed into the function. Ref: PowreShell Tool Time: Controlling Our Tools - [( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [Insert data into a SQL Server Table using Powershell using Invoke-SQLc]( Vishnu Gupthan from [SQLServerCentral.com]() How to insert data into a SQL Server Table using Powershell using Invoke-SQLcmd Commandlet Being a SQL Server DBA , while dealing with reporting and automation stuffs, it is quite needed to insert the data into SQL Server table using shell or batch scripts Here , we are going to use powershell to insert the data using a commandlet in Powershell called Invoke-SQLCmd. There are many other options also to do the same but we are focusing Invoke-SQLcmd to perform this in this session. What we are doing Insert the Services running in a windows server to a table in SQL Server. What we need SQL Server installed in the machine\Server. How we are doing 1. Take the output of the Get-Services command. 2. Insert the data to SQL Server table ‘ServiceTable’ Steps - This is how the Get-Service commandlet output looks like. Script to extract the details and insert into SQL server table. Powershell Script $services=Get-Service foreach($service in $services) { $ser=$service.Status $name=$service.name $disname=$service.DisplayName $insertquery=" INSERT INTO [dbo].[ServiceTable] ([Status] ,[Name] ,[DisplayName]) VALUES ('$ser' ,'$name' ,'$disname') GO " Invoke-SQLcmd -ServerInstance 'KILIKOOD-PC\MSSQLSERVER,1433' -query $insertquery -U sa -P test123 -Database Fantasy } Table ServiceTable looks like below : Notes : Normally Invoke-SQLcmd should work if you have SQL Server installed in the server as the SQL Server Management Objects will get installed along with it. If you still have any issues in loading Invoke-SQLcmd , please follow the below steps as referred in the link [click here]( - Install SQL Server 2008 R2 Management Objects using [Web PI]( (I'm not sure about versions prior to 2008 R2... if you have more info, please let us know in the comments) - Install 'Windows PowerShell Extensions for SQL Server' from the [Microsoft® SQL Server® 2008 R2 Feature Pack]( page (it's about halfway down the page). Make sure you pick the correct flavor for your instance (32 or 64 bit). - Run these two commands before calling invoke-sqlcmd in your script: Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 For SQL Server 2012, MSFT have [switched to a single module](. Run this instead: Import-Module SqlPs [More »]( 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]( : [SQL Server 2017 - Administration]( [Metldown and Spectre]( - Has anyone deployed any of the patches released for SQL 2016 and SQL 2017 to any of their environments. Is anyone... [Tool for SQL Server code tuning]( - Hello experts, Is there a free or a paid tool/software that can be used to provide t-sql recommendations or that can... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Performance issues post migration to 2016...looking for recommendations (Resolved)]( - Hello, We have been battling post go live over the weekend issues migrating to a 2 node 2016 instance.  We have... [exec xp_cmdshell]( - Hi, I am trying to execute below script to find available backup files for automate restore, but always the error is... [AlwaysON AG in SQL serer 2012]( - Hi All, I'm planning to setup AlwayON AG top of fail-over cluster feature enabled for two node replicas. Can you please suggest... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [ETL and data model design advice needed]( - I am often required to provide my organization with performance metrics (it is a health care organization so the metrics... [Update Table A when table B columns are updated and vice versa]( - How do i Update Table A columns when Table B is updated. Also I need to update Table B columns... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Efficient way of mirroring tables from one sql instance to another]( - I would like to know what would be an alternative solution to mirror tables from one sql instance to another... [Zip XML data]( - Hi Experts, We are creating an archival plan for one of our table which stores json data in nvarchar(max). We planned... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Ghost Process attacks again]( - Hi, Every now and then, a process (red arrow) executed by SA in our SQL Server 2012 instance blocks our system.... [In place OS upgrade of one node cluster]( - I currently have a one node cluster running Windows Server 2012 Standard and SQL Server 2012 standard. It is one... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Tying MAX() value to - This one is giving me fits. I'm querying mainframe data that has been imported to SQL. An "Invoice" table and an...]( [Compare two tables and fill the validfrom and c]( - hi, I would like to fill the fromdate and Todate dates of the UniqueConsumption2 table fromdate = Postingdate column value of table UniqueConsumption2 Todate = Next availaible postingdate column... [Need to convert lowercase characters to 'a' and uppercase to 'A']( - Hi, I have a requirement to anonymise the data in table by replacing all the lowercase characters to 'a' and uppercase... [Debit Close and Aging]( - Hi, I want to aging. drop table #TempStk CREATE TABLE #TempStk(     IDENTITY(1,1) NOT NULL,     NULL,     NULL,     NULL)    &nb --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [With Schemabinding]( - I use With Schemabinding whenever possible, on the theory that it is safer to have things locked up to prevent... --------------------------------------------------------------- [SQL Server 2008]( : [T-SQL (SS2K8)]( [Question Regarding Date logic]( - Hi, I need to get Year, monthname in three letters, startdate and enddate of the month for last two years... [DDL Trigger to Track All Database Changes? Permissions]( - Looking to implement the server level trigger below to track all DDL changes in the instance. How do I get... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [opening a rowset failed]( - hi i have ssis package , loading data from excel file into sql server table. package is working fine if I execute in SSDT when... --------------------------------------------------------------- [Database Design]( : [Relational Theory]( [A simple example which, after 15 years of relational design work, still breaks my brain - phone numbers]( - Every so often I find myself needing to build schema for something which has very little complexity in the "real... 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](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com