Easy way to copy Triggers

I'm looking for an easy way to copy triggers from my development database to
my production database without having to copy and paste the trigger to each
table.
You could generate a SQL script... e.g. open Enterprise Manager, right-click
a table, choose All Tasks > Generate SQL Script, choose all tables, and make
sure that on the options tab, you select "script triggers"... then you can
just modify the resulting script to only have the triggers, assuming all of
the tables actually exist in the production database.
You could also try using the system tables to generate a script to create
all triggers:
select sc.text + CHAR(13) + CHAR(10) + 'GO'
from
sysobjects so INNER JOIN
syscomments sc ON sc.id = so.id
where so.xtype='tr'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:pcIac.10178$lt2.7981@.newsread1.news.pas.earth link.net...
> I'm looking for an easy way to copy triggers from my development database
> to
> my production database without having to copy and paste the trigger to
> each
> table.
>

Easy way to copy Triggers

I'm looking for an easy way to copy triggers from my development database to
my production database without having to copy and paste the trigger to each
table.You could generate a SQL script... e.g. open Enterprise Manager, right-click
a table, choose All Tasks > Generate SQL Script, choose all tables, and make
sure that on the options tab, you select "script triggers"... then you can
just modify the resulting script to only have the triggers, assuming all of
the tables actually exist in the production database.
You could also try using the system tables to generate a script to create
all triggers:
select sc.text + CHAR(13) + CHAR(10) + 'GO'
from
sysobjects so INNER JOIN
syscomments sc ON sc.id = so.id
where so.xtype='tr'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Geoff" <cbsinc@.earthlink.net> wrote in message
news:pcIac.10178$lt2.7981@.newsread1.news.pas.earthlink.net...
> I'm looking for an easy way to copy triggers from my development database
> to
> my production database without having to copy and paste the trigger to
> each
> table.
>

easy trigger

I create trigger no table person. When I invoke sql command
insert into person values (... bla bala bal)
trigger is autommaticaly fired.
The trigger must log sql commads, so i must insert into table log this command, whos execute triger - insert into person values (... bla bala bal).
How I can do it?Make sure your database is set to allow cascading triggers, then your trigger should be able to both log its own insert and initiate other inserts.

blindman|||This in no problem.
Problem is:
How to find out what statement is making the trigger run and get this statement in trigger code

Originally posted by blindman
Make sure your database is set to allow cascading triggers, then your trigger should be able to both log its own insert and initiate other inserts.

blindman|||The trigger has no idea what caused the insert, update, or delete. The best you can do is reference some of the nyladic functions (look them up) that return login and user information. Then you can at least see WHO or WHAT LOGIN initiated the process.

blindman|||Well...you can tell what type...

INSERT: Rows in inserted, none in deleted
DELETE: Rows in deleted, mone in inserted
UPDATE: Rows in both

...|||The following example uses a table called PARENT as the base for INSERT TRIGGER:

if object_id('dbo.tblLog') is not null
drop table dbo.tblLog
go
create table dbo.tblLog (
EventType varchar(15) null,
Parameters int null,
EventInfo varchar(8000) null)
go
if object_id('dbo.trig_parent') is not null
drop trigger dbo.trig_parent
go
create trigger dbo.trig_parent on dbo.parent for insert as
set nocount on
declare @.cmd varchar(8000)
set @.cmd = 'create table #tbl (
EventType varchar(15) null,
Parameters int null,
EventInfo varchar(8000) null);'
set @.cmd = @.cmd + 'insert #tbl '
set @.cmd = @.cmd + 'exec ('
set @.cmd = @.cmd + char(39)+'dbcc inputbuffer (' + cast(@.@.spid as varchar(25)) + ') '
set @.cmd = @.cmd + 'with no_infomsgs' + char(39)
set @.cmd = @.cmd + ');'
set @.cmd = @.cmd + 'insert dbo.tblLog select * from #tbl'
exec (@.cmd)
go

Dynanic table creation

Hello friends
I want to have a trigger for creating table on dialy basis.
for e.g. I have a table say Data and on each day a new table is created like Data24Oct05 and so on.
please help for writing the trigger for the same.
thanks
This seems like a job for SQL Server Agent, not a trigger. I'dwrite a stored procedure to create the table, and schedule it via SQLServer Agent to run every day.
|||

Thank you very much.....
Could you please tell me how should I create dynamic table in the query? like, I have to append the today's date to a fixed string and create the table for that name.
And also where how to set the schedule for running this stored proc at the end of the day?

Please reply back

|||

swatib wrote:

Thank you very much.....
Couldyou please tell me how should I create dynamic table in the query?like, I have to append the today's date to a fixed string andcreate the table for that name.
And also where how to set the schedule for running this stored proc at the end of the day?

Please reply back

Your dynamic CREATE TABLE statement would look something like this:

DECLARE @.Sql varchar(200)
SELECT @.Sql = 'CREATE TABLE Data' + REPLACE(CONVERT(char(12),GETDATE(),113),' ','') + '(column1 varchar(10), column2 int)'
EXECUTE(@.Sql)

And you can use Enterprise Manager to access SQL Server Agent toschedule the job. Click on your server name, then chooseManagement. Under that, click on SQL Server Agent. Underthat, right-click on Jobs and choose New Job. Enter the nameyou'd like to call this job, enter a T-SQL step to EXECUTE your storedprocedure, and set the Schedule.

|||Thank you very much sir for the details reply and the solution.

Dynamic use of "inserted" and "deleted" in a trigger

Hello,
Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger?
I get an "invalid object name" with these statement.
set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
EXEC sp_executesql @.sqlcmd
Any other suggestions?
Thanks!
Per>'insert mydatabase.dbo.mytable select * from inserted'
what is dynamic here? No need, If i understand correctly
TRY THIS
INSERT INTO mydatabase.dbo.mytable
SELECT <COLUMN LIST AS IN my table order> from inserted
if schema is different or identity is in the mytables, it throws an error.
if you are expecting something else, do post
Regards
R.D
"Per Buus S?rensen" wrote:

> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger
?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||Sorry for the bad example, it should have been:
set @.sqlcmd = 'insert ' + @.db +'.dbo.' + @.table + ' select * from inserted'
EXEC sp_executesql @.sqlcmd
Per
"R.D" wrote:
> what is dynamic here? No need, If i understand correctly
> TRY THIS
> INSERT INTO mydatabase.dbo.mytable
> SELECT <COLUMN LIST AS IN my table order> from inserted
> if schema is different or identity is in the mytables, it throws an error.
> if you are expecting something else, do post
> Regards
> R.D
>
> "Per Buus S?rensen" wrote:
>|||Hi
No you cannot
"Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a
> trigger?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
as short, concise and efficient as possible because they run in a
transaction.
I find that the best way to create generic trigger code is to generate
it semi-automatically at design-time using the information_schema
views. This is very easy to do as long as the trigger code is identical
or similar in each case. If you prefer you can put your common code in
a stored proc and then insert the contents of the Inserted / Deleted
tables into local temporary table(s) that the proc can use.
Hope this helps.
David Portas
SQL Server MVP
--|||As Portas said, you have to use intermediate temp table because each exec
will have its own scope and inserted table is not in exec() scope.
well if you stick to using dynamic sql for peculiar reasons
use this
create table ##mytemptable<data definition same as inserted table)
insert into ##mytemptable select * from inserted
exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
drop table ##mytemptable
--
if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put N'
before string.
Regards
R.D
"Uri Dimant" wrote:

> Hi
> No you cannot
> "Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
> message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
>
>|||The "temp" solution was my first through about a work around, however I dont
know the definition of the source table, since users can change layout from
ERP system, so the trigger should be as general as possiple.
Is it possiple easierly to create a copy of the source table definition as a
temp table?
Per
"R.D" wrote:
> As Portas said, you have to use intermediate temp table because each exec
> will have its own scope and inserted table is not in exec() scope.
> well if you stick to using dynamic sql for peculiar reasons
> use this
> create table ##mytemptable<data definition same as inserted table)
> insert into ##mytemptable select * from inserted
> exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
> drop table ##mytemptable
> --
> if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put
N'
> before string.
> Regards
> R.D
> "Uri Dimant" wrote:
>|||I am aware of the performance issue about such a trigger, but it will be use
d
on smaller master data tables, which updates x other similar tables in other
databases.
Users can setup new databases them self, so flexibility is more important
than performance.
Per
"David Portas" wrote:

> No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
> as short, concise and efficient as possible because they run in a
> transaction.
> I find that the best way to create generic trigger code is to generate
> it semi-automatically at design-time using the information_schema
> views. This is very easy to do as long as the trigger code is identical
> or similar in each case. If you prefer you can put your common code in
> a stored proc and then insert the contents of the Inserted / Deleted
> tables into local temporary table(s) that the proc can use.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Like I suggested before, it sounds like code generation is the way to
go. Much simpler to maintain than dynamic code.
David Portas
SQL Server MVP
--|||I see that the server creates the temp table with correct definition, if it
doesn't exists.
So that solves my problem...so far.
Per
"Per Buus S?rensen" wrote:
> The "temp" solution was my first through about a work around, however I do
nt
> know the definition of the source table, since users can change layout fro
m
> ERP system, so the trigger should be as general as possiple.
> Is it possiple easierly to create a copy of the source table definition as
a
> temp table?
> Per
> "R.D" wrote:
>

dynamic trigger

I have created three trigger on three table. But the table names change
every year. Instead of changing 3 trigger every year, how can I change it
more efficient? Thanks.
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
Um, by not changing your table names?
|||Yes, I mean by not changing the table name in the triggers. Like a global
constant. Say I set up currentyear = 2004 and next year is I set up
currentyear = 2005 and I don't need to change 2004 to 2005 every where.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Um, by not changing your table names?
>
|||If you drop/recreate the tables, you must do the same with the triggers. You
could put all of the code in strings in the trigger , and use the exec
command... You might consider putting most of the common code in a stored
procedure, and calling the sp from the trigger.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>
|||Sorry, I clicked too soon..
It would probably be worthwhile for you to try to reconsider the design...
perhaps you could use the same tablename in production, and remove annual
data to a history table at the end of the year...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>
|||Hi
If you make the structure so complicated, then you need to live with high
maintenance.
Why don't you create one table, instead of one for each 2003, 2004, 2005 etc
and have one column that identifies the year. That would be a correct
relational database design.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Yes, I mean by not changing the table name in the triggers. Like a global
> constant. Say I set up currentyear = 2004 and next year is I set up
> currentyear = 2005 and I don't need to change 2004 to 2005 every where.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
>
|||Thanks, Mike. Unfortunately the table name is bound to the vendor system
and there is not much thing I can do with the table name. I just don't want
to change the trigger on this table and the table name changes every year
according to the year. Any workaround?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uRaQo2t1EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> If you make the structure so complicated, then you need to live with high
> maintenance.
> Why don't you create one table, instead of one for each 2003, 2004, 2005
> etc
> and have one column that identifies the year. That would be a correct
> relational database design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
>

dynamic trigger

I have created three trigger on three table. But the table names change
every year. Instead of changing 3 trigger every year, how can I change it
more efficient? Thanks.> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
Um, by not changing your table names?|||Yes, I mean by not changing the table name in the triggers. Like a global
constant. Say I set up currentyear = 2004 and next year is I set up
currentyear = 2005 and I don't need to change 2004 to 2005 every where.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
>> I have created three trigger on three table. But the table names change
>> every year. Instead of changing 3 trigger every year, how can I change
>> it
>> more efficient? Thanks.
> Um, by not changing your table names?
>|||If you drop/recreate the tables, you must do the same with the triggers. You
could put all of the code in strings in the trigger , and use the exec
command... You might consider putting most of the common code in a stored
procedure, and calling the sp from the trigger.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>|||Sorry, I clicked too soon..
It would probably be worthwhile for you to try to reconsider the design...
perhaps you could use the same tablename in production, and remove annual
data to a history table at the end of the year...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>|||Hi
If you make the structure so complicated, then you need to live with high
maintenance.
Why don't you create one table, instead of one for each 2003, 2004, 2005 etc
and have one column that identifies the year. That would be a correct
relational database design.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Yes, I mean by not changing the table name in the triggers. Like a global
> constant. Say I set up currentyear = 2004 and next year is I set up
> currentyear = 2005 and I don't need to change 2004 to 2005 every where.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
> >> I have created three trigger on three table. But the table names change
> >> every year. Instead of changing 3 trigger every year, how can I change
> >> it
> >> more efficient? Thanks.
> >
> > Um, by not changing your table names?
> >
> >
>|||Thanks, Mike. Unfortunately the table name is bound to the vendor system
and there is not much thing I can do with the table name. I just don't want
to change the trigger on this table and the table name changes every year
according to the year. Any workaround?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uRaQo2t1EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> If you make the structure so complicated, then you need to live with high
> maintenance.
> Why don't you create one table, instead of one for each 2003, 2004, 2005
> etc
> and have one column that identifies the year. That would be a correct
> relational database design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
>> Yes, I mean by not changing the table name in the triggers. Like a global
>> constant. Say I set up currentyear = 2004 and next year is I set up
>> currentyear = 2005 and I don't need to change 2004 to 2005 every where.
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
>> >> I have created three trigger on three table. But the table names
>> >> change
>> >> every year. Instead of changing 3 trigger every year, how can I
>> >> change
>> >> it
>> >> more efficient? Thanks.
>> >
>> > Um, by not changing your table names?
>> >
>> >
>>
>

dynamic trigger

I have created three trigger on three table. But the table names change
every year. Instead of changing 3 trigger every year, how can I change it
more efficient? Thanks.> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
Um, by not changing your table names?|||Yes, I mean by not changing the table name in the triggers. Like a global
constant. Say I set up currentyear = 2004 and next year is I set up
currentyear = 2005 and I don't need to change 2004 to 2005 every where.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Um, by not changing your table names?
>|||If you drop/recreate the tables, you must do the same with the triggers. You
could put all of the code in strings in the trigger , and use the exec
command... You might consider putting most of the common code in a stored
procedure, and calling the sp from the trigger.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>|||Sorry, I clicked too soon..
It would probably be worthwhile for you to try to reconsider the design...
perhaps you could use the same tablename in production, and remove annual
data to a history table at the end of the year...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:OWhhbjp1EHA.164@.TK2MSFTNGP10.phx.gbl...
> I have created three trigger on three table. But the table names change
> every year. Instead of changing 3 trigger every year, how can I change it
> more efficient? Thanks.
>|||Hi
If you make the structure so complicated, then you need to live with high
maintenance.
Why don't you create one table, instead of one for each 2003, 2004, 2005 etc
and have one column that identifies the year. That would be a correct
relational database design.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Yes, I mean by not changing the table name in the triggers. Like a global
> constant. Say I set up currentyear = 2004 and next year is I set up
> currentyear = 2005 and I don't need to change 2004 to 2005 every where.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:es1NG$p1EHA.3000@.TK2MSFTNGP15.phx.gbl...
>|||Thanks, Mike. Unfortunately the table name is bound to the vendor system
and there is not much thing I can do with the table name. I just don't want
to change the trigger on this table and the table name changes every year
according to the year. Any workaround?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uRaQo2t1EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> If you make the structure so complicated, then you need to live with high
> maintenance.
> Why don't you create one table, instead of one for each 2003, 2004, 2005
> etc
> and have one column that identifies the year. That would be a correct
> relational database design.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:#lthlpq1EHA.3376@.TK2MSFTNGP12.phx.gbl...
>