Generating a script for triggers only (not tables)

Hi,
I'd like to generate a script for only all the triggers in my database, but
not the Tables.
When I use the Generate script funtion in Enterprise manager, I can't enable
the OK unless all tables or all views have been enabled on the General tab.
Is there a way to save only all triggers to a script?
Thanks very much for any help here
AntAnt
For SQL Server 2000
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script Tables and Triggers, ignoring system
' tables and system generated triggers
For Each genObj In db.Tables
If genObj.SystemObject = False Then
For Each objTrigger In genObj.Triggers
If objTrigger.SystemObject = False Then
objTrigger.Script intOptions, StrFilePath
End If
Next
End If
Next
End Sub
Save the module as MyModule.
To call the procedure, open the Immediate window, type the following line,
and then press ENTER:Call
ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
For SQL Server 2005
http://dimantdatabasesolutions.blogspot.com/2007/06/triggers-definition.html
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:EFC9BFB2-6E17-4493-BACC-ECCF8ECB2B38@.microsoft.com...
> Hi,
> I'd like to generate a script for only all the triggers in my database,
> but
> not the Tables.
> When I use the Generate script funtion in Enterprise manager, I can't
> enable
> the OK unless all tables or all views have been enabled on the General
> tab.
> Is there a way to save only all triggers to a script?
> Thanks very much for any help here
> Ant

generate trigger automaticlly when create replicatoin?

Yes - for all merged tables there are triggers which'll
update the MSmerge_tombstone and MSmerge_contents tables.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi paul:
My problem is that i couldn't insert new datas into those table ,which
has trigger generated by replication,
so what i am suppose to do with this,? can i just simply delete trigger ?
The error message i got is :
Invalid object name 'dbo.MSmerge_contents'. couldn't use view or
function'.............' because of binding errors
Cheers
nick
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:047101c53439$7ae97e70$a601280a@.phx.gbl...
> Yes - for all merged tables there are triggers which'll
> update the MSmerge_tombstone and MSmerge_contents tables.
> HTH,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hi paul:
my problem is that i couldn't insert data into those tables have
triggers that generated by replication.
so what i am suppose to do with that? can i just simply delete those
trigger, and would it be effect when i doing replciation later?
cheers
nick
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:047101c53439$7ae97e70$a601280a@.phx.gbl...
> Yes - for all merged tables there are triggers which'll
> update the MSmerge_tombstone and MSmerge_contents tables.
> HTH,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Nick,
have you have removed the subscription? In this case try
using sp_removedbreplication to remove any lingering
traces. If you want the subscription to remain active,
then what error are you getting on an insert to the table?
Rgds,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||hi paul:
the subscription here is from PDA. the errore i got is :
Invalid object name 'dbo.MSmerge_tombstone'.
Couldn't use view or function'tsvw_D758c7b90dd14f.........'
because of binding errors.
by the way , i already delete the publication associated with this
DB.
Cheers
Nick
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:108801c53502$7ba206b0$a501280a@.phx.gbl...
> Nick,
> have you have removed the subscription? In this case try
> using sp_removedbreplication to remove any lingering
> traces. If you want the subscription to remain active,
> then what error are you getting on an insert to the table?
> Rgds,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Nick,
if sp_removedbreplication doesn't do the trick, then you'll have to remove
the triggers. Initially you could disable them all using sp_msforeachtable
"ALTER TABLE ? DISABLE TRIGGER ALL".
HTH,
Paul Ibison

Generate custom primary key fields

Hi all,

I've recently dabbled into the world of Stored procedures and Triggers but am having some issues trying to implement some functionality.

What I want to accomplish is to either create a SP or Trigger (I'm still trying to figure out the prime differece between them) that takes values in, concatenates them, adds an increment and then saves it into the assigned column.

Say for example I have a company name, and a region
Name:'Traders Inc"
Region:KEN (Kenya).

What I want my sp to do is take the first four letters of the name, add a dash, concat the region and add an increment starting from 001 thereby giving me TRAD-KEN-001. Incase I put in another entry, it should do the same and start it at 001 as well. Only when the first two variables are the same should the number be incremented. I.e if we have another entry
Name: Tradine Jewelers",
Region: Kenya;

It should calculate and give me a value TRAD-KEN-002. This is what I want my sp to do.

I can experiment and get the concats etc to work but my confusion kicks in on how I integrate 'If' statements and how SQL actually manipulates vinputted values and where (and how) I get the value of the last increment.

Values will be passed in from my Business Logic Layer but I am totally lost after that i.e. Should I use a SP or Trigger, how do I get the values into one column, how do I integrate the IF statemnets to get the result that I am getting. The only part I understand is the Insert but thats about it.

Any help on flow, functions and commands would be wonderful with where the GO and EXECs should go in and how it will store variables temporarily.

Thanks in Advance.

I'd just like someone to set me off in the right direction.

Thanks.

|||

Here is one idea, perhaps still 'half baked', so take it as a seed, not a fully grown solution.

Column for Name
Column for Region
Column for Sequence

Column for Key Value (Concatenated fields); this column will have a UNIQUE INDEX -which will serve 'almost' as good as a Primary Key -but will allow the initial INSERT to succeed even with NULL values.

Use a Trigger (the difference between a Trigger and a Stored Procedure is that a Stored Procedure executes when called, whereas a Trigger automatically executes upon change to data in a table.)

Create an FOR INSERT Trigger. When a new row is inserted, search for the max(Sequence) WHERE MyTable.Name = inserted.Name AND MyTable.Region = Inserted.Region. Add one to that value, put it in the Sequence Column, and then concatenate the three columns together to make your key value. Store in the Key value column. (It may be necessary to use an INSTEAD OF Trigger rather than a FOR INSERT Trigger.)

I hope that this helps point you in a direction that will work for you.

|||Hi,

Assuming the table structure is like
create table table1 (AreaName varchar(100), Region varchar(100), CodeGenerated varchar(100))

You can use something like this or you can convert it into trigger.

CREATE PROCEDURE InsertData
@.AreaName VARCHAR(100),
@.Region VARCHAR(100)
AS
BEGIN
DECLARE @.CodeGenerated VARCHAR(100), @.Counter varchar(10)

SET @.CodeGenerated = substring(@.Areaname, 1, 4) + '-' + substring(@.Region,1,3) + '-'

SELECT @.Counter = ISNULL(MAX(REPLACE(CodeGenerated,@.CodeGenerated, '')),0)+1 FROM Table1
WHERE AreaName = @.AreaName AND Region = @.region

SET @.CodeGenerated = UPPER(@.CodeGenerated) + REPLICATE('0',3-len(@.Counter)) + convert(VARCHAR,@.Counter)

INSERT table1 VALUES(@.AreaName, @.Region, @.CodeGenerated)
END

-- Code to execute stored procedure
EXEC InsertData @.AreaName = 'Traders Inc', @.Region = 'KEN'

SELECT * FROM table1

--Neeraj--|||

Awesome. Thanks alot for your responses.

Neeraj, by looking at the code and my newbie databases status, you just saved me 1 or 2 days of trial and error with this one.Code works perfect. Thanks a million!

I need to get my hands on some good sql books. Any book recommendations or links which I can start off with?

|||Hi kundalani,

Good to know that your problem is solved.

As per forums guidelines you should close the topic if it is able to solve your problem :)

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17861&SiteID=1

Generate Column List

Is it possible to generate a list of all column names in a table seperated
by a comma?
I want to do this because I am creating some triggers on a large number of
tables, with a large number of fields in each, and rather than type out my
list of fields I wanted to generate a list so I could just copy/past into my
triggers.
Thanks
Hi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.

Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>
|||Or a SELECT TOP 0 * would do ;-)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ugGZPWQWEHA.3988@.tk2msftngp13.phx.gbl...
Hi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.

Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>
|||Using the object browser window in SQL Query Analyzer may help you. Try it:
- Start Query Analyzer, connect to your database server.
- Click from the Menus: Tools | Object Browser | Show/Hide (or just hit F8).
This brings up the Object Browser pane.
- Navigate database object tree in Object Browser pane to table of interest.
- Right-click on that table and pick Script Object to Clipboard as Select |
Insert | Update | Delete as appropriate.
- Click in your query window and hit Control/V to paste the SQL statement.
You get a complete SQL statement with placeholders for things you will need
to supply.
A friend pointed this out to me a while back. Looks like there may be other
tricks available via the templates tab but I've not explored the
possibilities.
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>

Generate Column List

Is it possible to generate a list of all column names in a table seperated
by a comma?
I want to do this because I am creating some triggers on a large number of
tables, with a large number of fields in each, and rather than type out my
list of fields I wanted to generate a list so I could just copy/past into my
triggers.
ThanksHi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.

Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>|||Or a SELECT TOP 0 * would do ;-)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ugGZPWQWEHA.3988@.tk2msftngp13.phx.gbl...
Hi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.

Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>|||Using the object browser window in SQL Query Analyzer may help you. Try it:
- Start Query Analyzer, connect to your database server.
- Click from the Menus: Tools | Object Browser | Show/Hide (or just hit F8).
This brings up the Object Browser pane.
- Navigate database object tree in Object Browser pane to table of interest.
- Right-click on that table and pick Script Object to Clipboard as Select |
Insert | Update | Delete as appropriate.
- Click in your query window and hit Control/V to paste the SQL statement.
You get a complete SQL statement with placeholders for things you will need
to supply.
A friend pointed this out to me a while back. Looks like there may be other
tricks available via the templates tab but I've not explored the
possibilities.
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>

Generate Column List

Is it possible to generate a list of all column names in a table seperated
by a comma?
I want to do this because I am creating some triggers on a large number of
tables, with a large number of fields in each, and rather than type out my
list of fields I wanted to generate a list so I could just copy/past into my
triggers.
ThanksHi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.
:)
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>|||Or a SELECT TOP 0 * would do ;-)
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ugGZPWQWEHA.3988@.tk2msftngp13.phx.gbl...
Hi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.
:)
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>|||Using the object browser window in SQL Query Analyzer may help you. Try it:
- Start Query Analyzer, connect to your database server.
- Click from the Menus: Tools | Object Browser | Show/Hide (or just hit F8).
This brings up the Object Browser pane.
- Navigate database object tree in Object Browser pane to table of interest.
- Right-click on that table and pick Script Object to Clipboard as Select |
Insert | Update | Delete as appropriate.
- Click in your query window and hit Control/V to paste the SQL statement.
You get a complete SQL statement with placeholders for things you will need
to supply.
A friend pointed this out to me a while back. Looks like there may be other
tricks available via the templates tab but I've not explored the
possibilities.
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>