Can I use SQL to Update about 20 Triggers for 20 Tables from Text files...

Question

Can I use SQL to Update about 20 Triggers for 20 Tables from Text files...

john.steinbeck

Ten Centuries

Points: 1015
More actions
October 27, 2008 at 6:57 am

#70844

Hi,
I have 1 Script that Creates my triggers for me, but now I have to open 20 tables and then copy the updated Triggers into each trigger...
Can't I do some SQL Statement that says select these 20 tables, and update the this tables trigger with this trigger located "Y:\ALLL\NewTrigger4ColumnUpdates\trg_tbl_Factor_Adjustments_AuditUpdates.txt"????
I'm lazy...lol
John

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Even if the triggers are exactly the same, you'll still have to change the ON clause of the triggers, and you should probably change the names of each one as well. This would lend itself to dynamic sql and either a cursor or loop. (Or a really long dynamic SQL statement) Whether or not it's worth the time to alter the script in that manner is up to you.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

john.steinbeck Ten Centuries Points: 1015 More actions · Answer 2

The ON Clause is already done in each of the text files... below is the top portion of one of the txt files... as you can see everything is done for each trigger already...

Hey, couldn't I then, just open up an empty query and paste all the triggers in there and then just execute, rather than going to each table.... doh

USE [CPMTest]

GO

/****** Object: Trigger [dbo].[trg_tbl_Factor_Adjustments_AuditUpdates] Script Date: 10/10/2008 08:00:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET NOCOUNT ON

GO

ALTER TRIGGER [dbo].[trg_tbl_Factor_Adjustments_AuditUpdates]

ON [dbo].[tbl_Factor_Adjustments]

--Author: John Steinbeck

--Date: 24 Sept 08

--Purpose: This Trigger is designed to capture any column whose value is updated... you will specify what columns you want

--Version: v3

--Change Desc: See Workbook - Y:\ALLL\BuildSQLTriggerUpdateColumnsFunction.xls

FOR UPDATE

AS

---- Declarations

DECLARE @TableName VARCHAR(100)

DECLARE @KeyField VARCHAR(100)

DECLARE @KeyVal VARCHAR(100)

DECLARE @OldVal VARCHAR(500)

DECLARE @NewVal VARCHAR(500)

DECLARE @COL_NAME NVARCHAR(100)

DECLARE @SYSUSER VARCHAR(100)

DECLARE @getdate-2 DATETIME

DECLARE @XID VARCHAR(50)

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Yep, this is code, no need to open each table. You wouldn't do that anyway, the GUI just makes it seem you would.

I'd honestly just use SQLCMD, run them all through as separate scripts.

sqlcmd -Q "x:/xx.xx"

sqlcmd -Q "y:/yy.yy"

Garadin One Orange Chip Points: 29613 More actions · Answer 4

Hey, couldn't I then, just open up an empty query and paste all the triggers in there and then just execute, rather than going to each table.... doh

Heh... yep.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

john.steinbeck Ten Centuries Points: 1015 More actions · Answer 5

So this is what mine would have to be? This is new for me, just want to verify so as to not break something...lol

Thanks,

John

SQLCMD -Q "Y:/ALLL\NewTrigger4ColumnUpdates\trg_tblRptLevel_AuditUpdates.txt"