how to fire triggers during log shipping

Hi,
We are trying to impliment log shipping. During the log shipping the target database will have insert, update or delete trigger on some of it's table.
Can some one let me know will these trigger get fired during log shipping. if not the is there some way to fire these triggers.

Thanks,
manojTriggers on primary database will have no issues while Log shipping process is on, anyway the secondary server database will be in read-only mode so no affect.|||It means these triggers will never fire on secondary database.
is there any way I can make them fire.

Thanks

Originally posted by Satya
Triggers on primary database will have no issues while Log shipping process is on, anyway the secondary server database will be in read-only mode so no affect.|||Why do you want fire triggers on secondary database, as LS process will restore the transactions from primary database.|||Hi satya,

I need to explain you the scenario

We have two system with two seperate production database on two physicaly seperate servers. one of the production database is search intensive and the other is transaction intensive. There are few common tables in these two databases.

As the data in transaction intensive database changes we want to move this data to the search intensive database to keep in sync.

The client don't want replication as solution.

client is planning to implement the runtime Log shifting for failover database of Transaction intensive database.

So we want to take this opportunity to run triggers on this failover database to move data to search database. as this we think will keep the down time to zero.

any suggestions?

Regards
Manoj

Originally posted by Satya
Why do you want fire triggers on secondary database, as LS process will restore the transactions from primary database.

How to fire a trigger without changing table data

I have tables that I want to fire either an update or insert trigger on.

I could write a script containing a long list of inserts but I'm looking for
something simpler. Would isql work? Any special conditions to get it to
work?

I've tried tricks like 'update x set col = col' or 'update x set col = col +
'' '

All the alternatives seem to have problems. Any ideas?

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Try:

update MyTable
set
Col1 = 'x'
where
1 = 2

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John Smith" <nobody@.nowhere.com> wrote in message
news:1143510748_10105@.sp6iad.superfeed.net...
I have tables that I want to fire either an update or insert trigger on.

I could write a script containing a long list of inserts but I'm looking for
something simpler. Would isql work? Any special conditions to get it to
work?

I've tried tricks like 'update x set col = col' or 'update x set col = col +
'' '

All the alternatives seem to have problems. Any ideas?

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:SL0Wf.1028$m35.96044@.news20.bellglobal.com...
> Try:
> update MyTable
> set
> Col1 = 'x'
> where
> 1 = 2

Thanks, but it doesn't work.

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||> Thanks, but it doesn't work.

The script Tom posted works for me: Please expand on what you mean by 'it
doesn't work'.

CREATE TABLE MyTable(Col1 int)
GO

CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE AS
PRINT 'Trigger fired'
GO

UPDATE MyTable
SET Col1 = 'x'
WHERE 1 = 2
GO

DROP TABLE MyTable
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"John Smith" <nobody@.nowhere.com> wrote in message
news:1143512881_10135@.sp6iad.superfeed.net...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:SL0Wf.1028$m35.96044@.news20.bellglobal.com...
>> Try:
>>
>> update MyTable
>> set
>> Col1 = 'x'
>> where
>> 1 = 2
> Thanks, but it doesn't work.
>
> --== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:_s1Wf.9738$tN3.2012@.newssvr27.news.prodigy.ne t...
>> Thanks, but it doesn't work.
> The script Tom posted works for me: Please expand on what you mean by 'it
> doesn't work'.

Thanks for the help. The problem was due to NULL values in some columns.

The trigger was firing but not changing data.

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||>>The trigger was firing but not changing data.

Thats what your question says

Madhivanan

how to fine a hole in a records?

Hi all!

I need your help to realize algorithm for stored proc or trigger.

tool: MS SQL server 2000, T-SQL

TABLE:
[unique_id] [mynumber] [week]

[unique_id] - bigint,primary key, identity auto-increnment
[week] - int, 1-53, week number
[mynumber] - int, 1 - 7, for every week, daily record one per day, up
to 7 per week

so, for every week we have a mynumber from 1 to 7
or nothing (if no records for that day),

we can insert or delete mynubers in any order, at will

EXAMPLE:

week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value
= 4
week 2, mynumber 1,2,3,5,7 - so next mynumber = 4

QUESTION:

How to use _only_ T-SQL find a missed numbers for particular week when
I'm insert a records?

Thanks.
ChapaiPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Rows are not records and you have no relational key in your
pseudo-code. Ignoring that the design is fundamentally bad because you
should be using temporal datatypes for temporal data, your table should
have looked like this:

CREATE TABLE Foobar
(week_nbr INTEGER NOT NULL
CHECK(week_nbr > 0),
day_nbr INTEGER NOT NULL
CHECK(day_nbr BETWEEN 1 AND 7),
PRIMARY KEY(week_nbr, day_nbr));

>> for every week we have a day_nbr from 1 to 7 or nothing (if no
record [sic] for that day), we can insert or delete day_nbr in any
order, at will . . How to use _only_ T-SQL to find a missed number for
particular week when I am inserting records [sic]? <<

This is a little ugly looking, but it is fast.

CREATE PROCEDURE InsertNewFoobar (@.new_week_nbr INTEGER)
BEGIN
DECLARE @.new_day_nbr INTEGER;
SET @.new_day_nbr
= CASE WHEN 1 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 1
WHEN 2 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 2
WHEN 3 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 3
WHEN 4 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 4
WHEN 5 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 5
WHEN 6 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 6
WHEN 7 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 7
ELSE NULL END;

INSERT INTO Foobar (week_nbr, day_nbr)
VALUES (@.new_week_nbr, @.new_day_nbr);
-- if you have 7 days already, then you get a primary key violation
-- you gave no specs on how to handle it

END:
In Standard SQL, the CASE expression could be in the VALUES () list|||Chapai (racecar@.mail.ru) writes:
> tool: MS SQL server 2000, T-SQL
> TABLE:
> [unique_id] [mynumber] [week]
> [unique_id] - bigint,primary key, identity auto-increnment
> [week] - int, 1-53, week number
> [mynumber] - int, 1 - 7, for every week, daily record one per day, up
> to 7 per week

I don't see the point with unique_id. Judging from your description
(week, mynumber) is unique. Then they should be the primary key.

> we can insert or delete mynubers in any order, at will
> EXAMPLE:
> week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value
>= 4
> week 2, mynumber 1,2,3,5,7 - so next mynumber = 4
> QUESTION:
> How to use _only_ T-SQL find a missed numbers for particular week when
> I'm insert a records?

Search Google or the subject "Thinking about code or SP", a recent
thread in microsoft.public.sqlserver.programming for a whole range of
suggestions to a similar problem.

Since this problem is constrained to 1-7, here is a more simple-minded
solution:

SELECT MIN (n)
FROM (SELECT n = 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7) n
WHERE NOT EXISTS (SELECT *
FROM weeks w
WHERE w.weekno = @.weekno
AND n.n = w.mynumber)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!

Thanks, I already find the same solution with while

create proc stupidproc ( @.week as int )
as

declare @.mynumber as int, @.availablenumber as int
set @.mynumber = 1

WHILE @.mynumber < 8
begin
IF NOT EXISTS ( select mynumber from MYTABLE

where mynumber = @.mynumber and [week]=...@.week)

begin
set @.availablenumber = @.mynumber
break
end
else
set @.mynumber = @.mynumber + 1

CONTINUE
end

select @.availablenumber|||Hi!

Erland Sommarskog wrote:

> I don't see the point with unique_id. Judging from your description
> (week, mynumber) is unique. Then they should be the primary key.

Just personal rule - always get a unique,independent id. I use sql in
web development - so to create a lists, etc.

> Search Google or the subject "Thinking about code or SP", a recent
> thread in microsoft.public.sqlserver.programming for a whole range of
> suggestions to a similar problem.
that was my second step. But it take a lot of wasted time.

> Since this problem is constrained to 1-7, here is a more
simple-minded
> solution:
> SELECT MIN (n)
> FROM (SELECT n = 1
> UNION ALL
> SELECT 2
> UNION ALL
> SELECT 3
> UNION ALL
> SELECT 4
> UNION ALL
> SELECT 5
> UNION ALL
> SELECT 6
> UNION ALL
> SELECT 7) n
> WHERE NOT EXISTS (SELECT *
> FROM weeks w
> WHERE w.weekno = @.weekno
> AND n.n = w.mynumber)
Cool. That is elegant. thanks.
Select x union all - that's nice.|||>> already find the same solution with while .. <<

Wrong. You have a proprietary, procedural answer that is
computationally equal to what I gave you. This is a BIG difference and
until you can see this, you will always be a 3GL programmer writing in
3GL programs in some proprietary, non-portable SQL dialect.

The whole point of non-procedural languages is that you tell it WHAT
you want and it figures oiut HOW to do it. Looping is a HOW and not a
WHAT.|||Hi!

--CELKO-- wrote:
> Wrong. You have a proprietary, procedural answer that is
> computationally equal to what I gave you.

yeah, right. I'm agree with you and I'm appreciate for your ideas and
help. But my version is more flexible and compact. what I gonna do with
your algorithm if I need more than 7 numbers? what about 50? 100?
Using a "while" I need to change only one variable and code are still
readable.
Easy to change, easy to support.
Portability is not important at all, especially for me, I'm a web
developer, so MS SQL cover all my (and my customers) needs. MS Access
cover the rest.

> until you can see this, you will always be a 3GL programmer writing
in
> 3GL programs in some proprietary, non-portable SQL dialect.
Ok. This is a real world. If you use ASP/VBscript/C#/.NET - Oracle
hosting are too expensive, Mysql useless.

> The whole point of non-procedural languages is that you tell it WHAT
> you want and it figures oiut HOW to do it. Looping is a HOW and not
a
> WHAT.
Nope. The whole point of non-procedural languages, and all other
programming languages - is to help you to make a money quickly. ;-)|||>> But my version is more flexible and compact. <<

No, your procedural coding is weak, too. Here is your algorithm in
SQL/PSM, which you can translate into dialect.

CREATE PROCEDURE StupidProc (IN my_week INTEGER)
LANGUAGE SQL
BEGIN
DECLARE answer_nbr INTEGER;
SET answer_nbr = 1;
WHILE answer_nbr < 8
DO IF NOT EXISTS
(SELECT *
FROM Foobar
WHERE day_nbr = answer_nbr
AND week_nbr = my_week)
THEN RETURN answer_nbr;
ELSE SET answer_nbr = answer_nbr + 1;
END IF;
END WHILE;
RETURN answer_nbr; -- 8 is an error
END;

The use of extra variables and the hidden GOTO's in BREAK and CONTINUE
would cost you points in any freshman programming class.

>> what I gonna do with your algorithm if I need more than 7 numbers?
what about 50? 100? <<

Use a Sequence table instead of a constructed table expression. Here
is a general version with pure Standard SQL

SELECT MIN (n)
FROM (SELECT seq FROM Sequence WHERE seq <= :n)
EXCEPT
(SELECT day_nbr
FROM Weeks AS
WHERE W.week_nbr = my_weeknbr) AS N(n);

>> The whole point of non-procedural languages, and all other
programming languages - is to help you to make money quickly. ;-) <<
And the only way you can do this is with bad programming??|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1105201718.985293.286980@.c13g2000cwb.googlegr oups.com...
>> >> The whole point of non-procedural languages, and all other
> programming languages - is to help you to make money quickly. ;-) <<
> And the only way you can do this is with bad programming??

Of course... that way the customer keeps coming back to you... until they
figure out they're paying too much. :-)|||Chapai (racecar@.mail.ru) writes:
> Just personal rule - always get a unique,independent id. I use sql in
> web development - so to create a lists, etc.

Not sure that I see the point, but as long as you have a UNIQUE constraint
on the real primary key, that's alright. Without the UNIQUE constraint,
you're putting the integrity of your database at stake.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!

--CELKO-- wrote:
> No, your procedural coding is weak, too. Here is your algorithm in
> SQL/PSM, which you can translate into dialect.

I have a better idea for you - just put your code into query analyzer
and try to execute it. Oops! It does not work? Why?
Open your eyes and read the group name. DB2? Super-duper-sql-theory?
Microsoft does not support this standard in sql server 2000.
Period.

> The use of extra variables and the hidden GOTO's in BREAK and
CONTINUE
> would cost you points in any freshman programming class.
Show me other faster and better way to use "while" in ms sql server
2000.
I'm wait.

> Use a Sequence table instead of a constructed table expression. Here
> is a general version with pure Standard SQL
aha. Hundreds sets like set @.i1 = 1, @.i2=2, tables, temporary tables,
cross calls, .. Sure. Sommarskog's sample was finer and more
interesting.

> And the only way you can do this is with bad programming??
It works? Works, fast? Fast. Simple? Simple. What else? Portability.
Strict adherence to standards. Ok. I'm not a student with ideas and not
an old professor with grey bolls - I'm MS web developer with hourly
rate.
Holy wars linux vs windows, c vs pascal. :-) You work on salary - right?|||Hi!

Erland Sommarskog wrote:

> Not sure that I see the point, but as long as you have a UNIQUE
constraint
> on the real primary key, that's alright. Without the UNIQUE
constraint,
> you're putting the integrity of your database at stake.

I'm a web developer. :-) Tomorrow customer can says to change
everything, or major part of logic. And I will get just couple hours to
realize that. other paradigm.|||Chapai (racecar@.mail.ru) writes:
>> Not sure that I see the point, but as long as you have a UNIQUE
>> constraint on the real primary key, that's alright. Without the UNIQUE
>> constraint, you're putting the integrity of your database at stake.
> I'm a web developer. :-) Tomorrow customer can says to change
> everything, or major part of logic. And I will get just couple hours to
> realize that. other paradigm.

If you work under these cirumstances, it's even more important to
have your constraints right. Stressed development, unevitably leads
to bugs creeping in. Constraint is a means of preventing at least some
of these bugs causing bad data to be persisted.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!

Erland Sommarskog wrote:

> If you work under these cirumstances, it's even more important to
> have your constraints right. Stressed development, unevitably leads

Ok. How you can link other tables without unique constraint?
Most fields are not unique, so, easy to get one guaranteed simple
unique field to reference. Or you know the other method?

table from discussed example (up to 7 workouts_num per week, for every
trainer/customer):

workout(workout_id, workout_num , workout_week, program_id, client_id)
and need to organise relations to table
program(program_id,program_name,trainer_id), table
exrcise(exercise_id,exercise_name),
table exercise_workout_link(workout_id,exercise_id)|||Chapai (racecar@.mail.ru) writes:
> Ok. How you can link other tables without unique constraint?

That's kind of difficult. Then again, I suggested that it was a
UNIQUE constraint that you should add to your table.

> Most fields are not unique, so, easy to get one guaranteed simple
> unique field to reference. Or you know the other method?
> table from discussed example (up to 7 workouts_num per week, for every
> trainer/customer):
> workout(workout_id, workout_num , workout_week, program_id, client_id)
> and need to organise relations to table
> program(program_id,program_name,trainer_id), table
> exrcise(exercise_id,exercise_name),
> table exercise_workout_link(workout_id,exercise_id)

It looks as if foreign-key constraint from workout to progam would be
possible. But not knowing the business rules, that is of course impossible
to tell.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

How to find triggers in a sql server 7 ?

Hello there
Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
didn't wrote it in the DB documentation.
So now i am standing in front of SQL 7 user DB and want to modify it and
have no idea what the name of it is and how to access it or anything ?
what do I do to find it ? (I used informationschema and it doesn't have
any trigger options in it...) how do I find all triggers in a sql 7 user
db ? how to modify it ?
please help !Schema: How do I show all the triggers in a database?
http://www.aspfaq.com/show.asp?id=2105
AMB
"Simo Sentissi" wrote:
> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
>|||"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:eg4ienzyEHA.2572@.tk2msftngp13.phx.gbl...
> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
SELECT * FROM sysobjects WHERE TYPE = 'TR'
This will give you a list of triggers.
For each trigger row returned, you can find out which table it is attached
to by running the following query and using the parent_obj ID value from the
list of triggers returned.
SELECT * FROM sysobjects WHERE ID = <parent_obj>
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||select *
from sysobjects
where type = 'TR'
You modify the trigger using alter trigger. See books online
topic Alter Trigger for more information.
-Sue
On Mon, 15 Nov 2004 10:37:53 -0700, Simo Sentissi
<simo@.sentissiweb.com> wrote:
>Hello there
>Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
>didn't wrote it in the DB documentation.
>So now i am standing in front of SQL 7 user DB and want to modify it and
>have no idea what the name of it is and how to access it or anything ?
>what do I do to find it ? (I used informationschema and it doesn't have
>any trigger options in it...) how do I find all triggers in a sql 7 user
>db ? how to modify it ?
>please help !sql

How to find triggers in a sql server 7 ?

Hello there
Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
didn't wrote it in the DB documentation.
So now i am standing in front of SQL 7 user DB and want to modify it and
have no idea what the name of it is and how to access it or anything ?
what do I do to find it ? (I used informationschema and it doesn't have
any trigger options in it...) how do I find all triggers in a sql 7 user
db ? how to modify it ?
please help !
Schema: How do I show all the triggers in a database?
http://www.aspfaq.com/show.asp?id=2105
AMB
"Simo Sentissi" wrote:

> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
>
|||"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:eg4ienzyEHA.2572@.tk2msftngp13.phx.gbl...
> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
SELECT * FROM sysobjects WHERE TYPE = 'TR'
This will give you a list of triggers.
For each trigger row returned, you can find out which table it is attached
to by running the following query and using the parent_obj ID value from the
list of triggers returned.
SELECT * FROM sysobjects WHERE ID = <parent_obj>
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||select *
from sysobjects
where type = 'TR'
You modify the trigger using alter trigger. See books online
topic Alter Trigger for more information.
-Sue
On Mon, 15 Nov 2004 10:37:53 -0700, Simo Sentissi
<simo@.sentissiweb.com> wrote:

>Hello there
>Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
>didn't wrote it in the DB documentation.
>So now i am standing in front of SQL 7 user DB and want to modify it and
>have no idea what the name of it is and how to access it or anything ?
>what do I do to find it ? (I used informationschema and it doesn't have
>any trigger options in it...) how do I find all triggers in a sql 7 user
>db ? how to modify it ?
>please help !

How to find triggers in a sql server 7 ?

Hello there
Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
didn't wrote it in the DB documentation.
So now i am standing in front of SQL 7 user DB and want to modify it and
have no idea what the name of it is and how to access it or anything ?
what do I do to find it ? (I used informationschema and it doesn't have
any trigger options in it...) how do I find all triggers in a sql 7 user
db ? how to modify it ?
please help !Schema: How do I show all the triggers in a database?
http://www.aspfaq.com/show.asp?id=2105
AMB
"Simo Sentissi" wrote:

> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
>|||"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:eg4ienzyEHA.2572@.tk2msftngp13.phx.gbl...
> Hello there
> Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
> didn't wrote it in the DB documentation.
> So now i am standing in front of SQL 7 user DB and want to modify it and
> have no idea what the name of it is and how to access it or anything ?
> what do I do to find it ? (I used informationschema and it doesn't have
> any trigger options in it...) how do I find all triggers in a sql 7 user
> db ? how to modify it ?
> please help !
SELECT * FROM sysobjects WHERE TYPE = 'TR'
This will give you a list of triggers.
For each trigger row returned, you can find out which table it is attached
to by running the following query and using the parent_obj ID value from the
list of triggers returned.
SELECT * FROM sysobjects WHERE ID = <parent_obj>
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||select *
from sysobjects
where type = 'TR'
You modify the trigger using alter trigger. See books online
topic Alter Trigger for more information.
-Sue
On Mon, 15 Nov 2004 10:37:53 -0700, Simo Sentissi
<simo@.sentissiweb.com> wrote:

>Hello there
>Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I
>didn't wrote it in the DB documentation.
>So now i am standing in front of SQL 7 user DB and want to modify it and
>have no idea what the name of it is and how to access it or anything ?
>what do I do to find it ? (I used informationschema and it doesn't have
>any trigger options in it...) how do I find all triggers in a sql 7 user
>db ? how to modify it ?
>please help !

How to find out row that was just inserted?

hi folks,
Is it possible to find out the row which was just inserted in a table? I am creating a trigger and i m trying to access the row which was just inserted into the table. Any suggestions? Thanks a million!
GayathriWithin a trigger you can access the newly inserted/updated/deleted fields by referencing virtual tables named:
"inserted" - holds new and updated records
"deleted" - holds deleted records

You just reference these tables as you would any permanent table:

select count(*)
from YOURTABLE
inner join INSERTED
on YOURTABLE.PRIMARYKEY = INSERTED.PRIMARYKEY

blindman|||My code is as follows

DECLARE job_number_cursor CURSOR FOR
SELECT JobNumber
FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
where JobNumber = Inserted.JobNumber
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor

But i get an error saying
"The column prefix 'Inserted' does not match with a table name or alias name used in the query. "

Is there any other syntax that i am missing?|||try this...

DECLARE job_number_cursor CURSOR FOR
SELECT JobNumber
FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
where JobNumber = (select Inserted.JobNumber from inserted)
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor|||Did modify to the code below but again it says "Invalid Object Name 'Inserted':(

Originally posted by rokslide
try this...

DECLARE job_number_cursor CURSOR FOR
SELECT JobNumber
FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
where JobNumber = (select Inserted.JobNumber from inserted)
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor|||this is the suggestion from MS...

CREATE TRIGGER Trigger1 ON dbo.TableA
FOR INSERT
AS
DECLARE @.var1 INT
DECLARE @.VarA int

SELECT @.VarA = SELECT col2 FROM inserted
DECLARE cursor1 CURSOR FOR
SELECT col1
FROM TableB
WHERE col1 = @.VarA

OPEN cursor1
FETCH NEXT FROM cursor1 INTO @.var1

CLOSE cursor1
DEALLOCATE cursor1
GO|||So modifying it for your purposes...

DECLARE @.SearchJobNumber int
DECLARE @.job_number int
SELECT @.JobNumber = SELECT JobNumber from inserted

DECLARE job_number_cursor CURSOR FOR
SELECT JobNumber
FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
where JobNumber = @.JobNumber
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor|||Here is how to rewrite your original statement to avoid the error you got:

DECLARE job_number_cursor CURSOR FOR SELECT JobNumber FROM Inserted
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor

The big question is why you are putting a cursor in a trigger. This is the database equivalent of pouring sewage into your gas tank.

blindman|||Cursor in trigger? Do not think it is good idea...|||I got the impression that things are getting on the wrong path. Summa summarum, the answer for your original question is given by Blindman in his first post:

select count(*)
from YOURTABLE
inner join INSERTED
on YOURTABLE.PRIMARYKEY = INSERTED.PRIMARYKEY

But this should be used in the trigger written on the table which you are interested in. Got the picture?
You probably get the error "Invalid Object Name 'Inserted'" because you are not using the code in the wright place.

Best regards!|||Can you not just use the @.@.IDENTITY property?

Or does this not work in a trigger?