tsql wrap the following in a transaction and roll it back on error: -- Create Comment…
- User
tsql wrap the following in a transaction and roll it back on error: -- Create Comment Categories table IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StudentCommentCategories') BEGIN CREATE TABLE StudentCommentCategories ( Id INT IDENTITY(1,1) PRIMARY KEY, Text NVARCHAR(255) NOT NULL, Active BIT NOT NULL DEFAULT 1 ); END;
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'General comment') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('General comment', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Employment info') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Employment info', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Pay & agreements') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Pay & agreements', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Feedback & Evaluation') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Feedback & Evaluation', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Screening & interview notes') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Screening & interview notes', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Sickness & absence') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Sickness & absence', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Status update') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Status update', 1);
IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Accidents, health & safety') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Accidents, health & safety', 1);
-- Add Category to Comment IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CommentsOnStudent' AND COLUMN_NAME = 'StudentCommentCategoriesId') BEGIN ALTER TABLE CommentsOnStudent ADD StudentCommentCategoriesId INT NOT NULL DEFAULT 1; END;
IF NOT EXISTS ( SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_CommentsOnStudent_StudentCommentCategories' ) BEGIN ALTER TABLE CommentsOnStudent ADD CONSTRAINT FK_CommentsOnStudent_StudentCommentCategories FOREIGN KEY (StudentCommentCategoriesId) REFERENCES StudentCommentCategories(Id); END
IF NOT EXISTS ( SELECT 1 FROM sys.indexes WHERE name = 'IX_CommentsOnStudent_StudentCommentCategoriesId' AND object_id = OBJECT_ID('CommentsOnStudent') ) BEGIN CREATE INDEX IX_CommentsOnStudent_StudentCommentCategoriesId ON CommentsOnStudent (StudentCommentCategoriesId); END
Assistant
Here's the T-SQL script wrapped in a transaction with error handling and rollback:
BEGIN TRY BEGIN TRANSACTION; -- Create Comment Categories table IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StudentCommentCategories') BEGIN CREATE TABLE StudentCommentCategories ( Id INT IDENTITY(1,1) PRIMARY KEY, Text NVARCHAR(255) NOT NULL, Active BIT NOT NULL DEFAULT 1 ); END; IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'General comment') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('General comment', 1); IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Employment info') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Employment info', 1); IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Pay & agreements') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Pay & agreements', 1); IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Feedback & Evaluation') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Feedback & Evaluation', 1); IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Screening & interview notes') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Screening & interview notes', 1); IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Sickness & absence') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Sickness & absence', 1); IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Status update') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Status update', 1); IF NOT EXISTS (SELECT 1 FROM StudentCommentCategories WHERE Text = 'Accidents, health & safety') INSERT INTO StudentCommentCategories (Text, Active) VALUES ('Accidents, health & safety', 1); -- Add Category to Comment IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CommentsOnStudent' AND COLUMN_NAME = 'StudentCommentCategoriesId') BEGIN ALTER TABLE CommentsOnStudent ADD StudentCommentCategoriesId INT NOT NULL DEFAULT 1; END; IF NOT EXISTS ( SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_CommentsOnStudent_StudentCommentCategories' ) BEGIN ALTER TABLE CommentsOnStudent ADD CONSTRAINT FK_CommentsOnStudent_StudentCommentCategories FOREIGN KEY (StudentCommentCategoriesId) REFERENCES StudentCommentCategories(Id); END IF NOT EXISTS ( SELECT 1 FROM sys.indexes WHERE name = 'IX_CommentsOnStudent_StudentCommentCategoriesId' AND object_id = OBJECT_ID('CommentsOnStudent') ) BEGIN CREATE INDEX IX_CommentsOnStudent_StudentCommentCategoriesId ON CommentsOnStudent (StudentCommentCategoriesId); END COMMIT TRANSACTION; PRINT 'Transaction completed successfully.'; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), @ErrorSeverity INT = ERROR_SEVERITY(), @ErrorState INT = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH;This script does the following:
- Wraps the entire script in a
BEGIN TRYblock - Starts a transaction with
BEGIN TRANSACTION - Performs all the original operations
- If all operations succeed, commits the transaction with
COMMIT TRANSACTION - If an error occurs, the
CATCHblock will:- Roll back the transaction if it's still active
- Capture the error details
- Re-raise the error with its original severity and state
This ensures that either all operations complete successfully, or none of them are applied if an error occurs.
- Wraps the entire script in a
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.