CODEWITHSUNDEEP
sql-servert-sqldeployment
koryakinp
koryakinp

Reputation: 4135

T-SQL post-deployment script run only once

I have Tasks table with a duration column. I want to create a post-deployment script which decrements the value of that column by 1. 

UPDATE Tasks SET duration = duration - 1

How can I make sure that the script will not run every time I publish the project, but will run only once. Is there a way to tell SQL Server to remember that the script already run before ?

Upvotes: 0

Views: 783

Answers (2)

jspcal
jspcal

Reputation: 51934

The post-deployment script is designed to be executed every time the project is deployed. The scripts should be repeatable. You can implement your own guard logic within your script to make sure it only runs when appropriate. In this case, you can check for a flag in a separate state table that tracks whether or not you've already performed the update. For instance:

-- Create a state table with a single row

create table deployment_script_state (duration_was_updated int);
insert into deployment_script_state values (0);

-- Perform the operation if the update hasn't been done

update t
set t.duration = t.duration - 1
from Tasks t
join deployment_script_state d
on d.duration_was_updated = 0

-- Set update flag to done

update deployment_script_state
set duration_was_updated = 1

Upvotes: 1

SQLApostle
SQLApostle

Reputation: 590

My way of implementing this uses a project variable

As an example :

IF '$(DeployJobs)' = 'true'
BEGIN
    PRINT 'The Jobs will be deployed as the DeployJobs flag has been set'
    ......
END

This allows me to control using deployment variables whether the script will run or not

Upvotes: 0

Related Questions