how to insert several insert commands, triggers?

Hello, what i want is simple.

This is a simple forum, it has several topics (that the users can create), when a user create a topic, its stored in forum_topics. The user can then view the topic and post a response that is store in forum_answer, the user can also add this to his favorite list, forum_favorites is simple, contains a TopicID that refers to the topic, a username of the user that has the topic on his favorite list and a auto increment id to be able to delete specified topic favorites.

Now my question is: when a user posts a answer to Topic X, i want a predefined message to be sent to post_inbox for all the users that has Topic X in their favorite list.

How can i get MS SQL 2005 to get all the users from Topic X and then loop thru them and insert a new post into post_inbox?

Patrick

Hello,

to insert multiple rows you can use select instead of values in the insert statement. So there is no need for a loop. It would be something like this:

INSERT INTO post_inbox (TopicID, UserName, Message)SELECT ( TopicId,--Is always the ID of Topic X, because of the WHERE UserName,--Is every user that is subscribed to Topic X'Hey, Topix X has a new answer, check it out!'--Maybe a message in their inbox?)FROM forum_favoritesWHERE TopicId = 123--ID op Topic X

So the select will select all users subscribed to Topic X, and you use that as the values for the insert.

Good luck!

|||

Hi Pafo,

From your description, I understand that you need to give each one, whose favorite list has Topic X, a message when any one replies to Topic X.

This can be done in many ways.

1. In the page code, when some one posts a reply, we can invoke some method to inform the observers. This will be more flexible in the logic layer.
2. If you need to do this in the database, we can use Triggers or we can extend the reply stored procedure to do this directly.

In my opinion, option 1 will be better, since users can customize whether to receive this message on their own.

HTH. If anything is unclear, please feel free to mark it as Not Answered and post your reply. Thanks!

How to import a table with primary and triggers

Hi,
When I import a table, it only imports the data without the primary key
and triggers. How to import a table with primary key and triggers?
Thanks
kaiUse Bulk Insert with the following options. Please see BOL for more
details.
CHECK_CONSTRAINTS
Specifies that any constraints on table_name are checked during the
bulk copy operation. By default, constraints are ignored.
FIRE_TRIGGERS
Specifies that any insert triggers defined on the destination table
will execute during the bulk copy operation. If FIRE_TRIGGERS is not
specified, no insert triggers will execute.
Mel|||Thanks Mel
Kai
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1144335965.378137.78020@.v46g2000cwv.googlegroups.com...
> Use Bulk Insert with the following options. Please see BOL for more
> details.
> CHECK_CONSTRAINTS
> Specifies that any constraints on table_name are checked during the
> bulk copy operation. By default, constraints are ignored.
> FIRE_TRIGGERS
> Specifies that any insert triggers defined on the destination table
> will execute during the bulk copy operation. If FIRE_TRIGGERS is not
> specified, no insert triggers will execute.
> Mel
>

How to import a table with primary and triggers

Hi,
When I import a table, it only imports the data without the primary key
and triggers. How to import a table with primary key and triggers?
Thanks
kaiUse Bulk Insert with the following options. Please see BOL for more
details.
CHECK_CONSTRAINTS
Specifies that any constraints on table_name are checked during the
bulk copy operation. By default, constraints are ignored.
FIRE_TRIGGERS
Specifies that any insert triggers defined on the destination table
will execute during the bulk copy operation. If FIRE_TRIGGERS is not
specified, no insert triggers will execute.
Mel|||Thanks Mel
Kai
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1144335965.378137.78020@.v46g2000cwv.googlegroups.com...
> Use Bulk Insert with the following options. Please see BOL for more
> details.
> CHECK_CONSTRAINTS
> Specifies that any constraints on table_name are checked during the
> bulk copy operation. By default, constraints are ignored.
> FIRE_TRIGGERS
> Specifies that any insert triggers defined on the destination table
> will execute during the bulk copy operation. If FIRE_TRIGGERS is not
> specified, no insert triggers will execute.
> Mel
>

How to import a table with primary and triggers

Hi,
When I import a table, it only imports the data without the primary key
and triggers. How to import a table with primary key and triggers?
Thanks
kai
Use Bulk Insert with the following options. Please see BOL for more
details.
CHECK_CONSTRAINTS
Specifies that any constraints on table_name are checked during the
bulk copy operation. By default, constraints are ignored.
FIRE_TRIGGERS
Specifies that any insert triggers defined on the destination table
will execute during the bulk copy operation. If FIRE_TRIGGERS is not
specified, no insert triggers will execute.
Mel
|||Thanks Mel
Kai
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1144335965.378137.78020@.v46g2000cwv.googlegro ups.com...
> Use Bulk Insert with the following options. Please see BOL for more
> details.
> CHECK_CONSTRAINTS
> Specifies that any constraints on table_name are checked during the
> bulk copy operation. By default, constraints are ignored.
> FIRE_TRIGGERS
> Specifies that any insert triggers defined on the destination table
> will execute during the bulk copy operation. If FIRE_TRIGGERS is not
> specified, no insert triggers will execute.
> Mel
>

How to ignore sp_runwebtask errors?

Hi,
I have some tables in my database that, upon modification, write a file
to disk with data. I have create the triggers that handle this, using
the sp_makewebtask stored procedure.
For these triggers to work, I needed to login from my application with
integrated security. This is not a problem and works fine.
Sometimes I also need to do some maintenance on the tables, and for
this purpose I cannot use integrated security (because I connect
through vpn an am not on the actual machine). When i then change
something on the tables I get an error: "SQL Web Assistant: Could not
open the output file".
The fact that if I'm doing maintenance, the file is not created, is not
a problem, so I hope that one of the following solutions is possible:
- Build error handling into the trigger that simply ignores the error
- Detect in the trigger that the current user is not a windows user,
and then skip the creation of the file
- something else.
What could be a solution for my problem?
BTW We are using SQL 2000 on Windows 2003 Server.
Regards, FelixFelix,

> - Detect in the trigger that the current user is not a windows user,
> and then skip the creation of the file
You can use SUSER_SNAME() to determine this.
IF SUSER_SNAME() = 'MySQLServerLogin' ...
Robert
"felix planjer" <fplanjer@.gmail.com> wrote in message
news:1143541278.508476.286150@.e56g2000cwe.googlegroups.com...
> Hi,
> I have some tables in my database that, upon modification, write a file
> to disk with data. I have create the triggers that handle this, using
> the sp_makewebtask stored procedure.
> For these triggers to work, I needed to login from my application with
> integrated security. This is not a problem and works fine.
> Sometimes I also need to do some maintenance on the tables, and for
> this purpose I cannot use integrated security (because I connect
> through vpn an am not on the actual machine). When i then change
> something on the tables I get an error: "SQL Web Assistant: Could not
> open the output file".
> The fact that if I'm doing maintenance, the file is not created, is not
> a problem, so I hope that one of the following solutions is possible:
> - Build error handling into the trigger that simply ignores the error
> - Detect in the trigger that the current user is not a windows user,
> and then skip the creation of the file
> - something else.
> What could be a solution for my problem?
> BTW We are using SQL 2000 on Windows 2003 Server.
> Regards, Felix
>

How to identify when the values are going on particular record

Hi,
I want to identify, when the values are modified/updated on the particular
row in a table, i am not using triggers in this table.
Please advise me, any options in SQL Profiler Trace to find out this.
rgds,
SouraSouRa
Which version of SQL Server you are using?
In SQL Server 2005 there is new OUTPUT clause to track changes
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> Hi,
> I want to identify, when the values are modified/updated on the particular
> row in a table, i am not using triggers in this table.
> Please advise me, any options in SQL Profiler Trace to find out this.
> rgds,
> Soura|||Hi,
I am using Sql Server 2000, any options in Sql 2000
rgds,
Soura
"Uri Dimant" wrote:
> SouRa
> Which version of SQL Server you are using?
> In SQL Server 2005 there is new OUTPUT clause to track changes
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> > Hi,
> >
> > I want to identify, when the values are modified/updated on the particular
> > row in a table, i am not using triggers in this table.
> >
> > Please advise me, any options in SQL Profiler Trace to find out this.
> >
> > rgds,
> > Soura
>
>

How to identify when the values are going on particular record

Hi,
I want to identify, when the values are modified/updated on the particular
row in a table, i am not using triggers in this table.
Please advise me, any options in SQL Profiler Trace to find out this.
rgds,
SouraSouRa
Which version of SQL Server you are using?
In SQL Server 2005 there is new OUTPUT clause to track changes
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> Hi,
> I want to identify, when the values are modified/updated on the particular
> row in a table, i am not using triggers in this table.
> Please advise me, any options in SQL Profiler Trace to find out this.
> rgds,
> Soura|||Hi,
I am using Sql Server 2000, any options in Sql 2000
rgds,
Soura
"Uri Dimant" wrote:

> SouRa
> Which version of SQL Server you are using?
> In SQL Server 2005 there is new OUTPUT clause to track changes
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
>
>

How to identify when the values are going on particular record

Hi,
I want to identify, when the values are modified/updated on the particular
row in a table, i am not using triggers in this table.
Please advise me, any options in SQL Profiler Trace to find out this.
rgds,
Soura
SouRa
Which version of SQL Server you are using?
In SQL Server 2005 there is new OUTPUT clause to track changes
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
> Hi,
> I want to identify, when the values are modified/updated on the particular
> row in a table, i am not using triggers in this table.
> Please advise me, any options in SQL Profiler Trace to find out this.
> rgds,
> Soura
|||Hi,
I am using Sql Server 2000, any options in Sql 2000
rgds,
Soura
"Uri Dimant" wrote:

> SouRa
> Which version of SQL Server you are using?
> In SQL Server 2005 there is new OUTPUT clause to track changes
>
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:1F2E9597-6D0F-431F-89D0-FAB4235F398C@.microsoft.com...
>
>

How to hide triggers from SQL Server?

Hi,
I wonder whether there is a way to temporarily hide triggers from SQL Server
2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want t
o
do this is because even though I disable triggers, SQL Server will take time
s
to evaluate whether the trigger is disabled or not. Based on my own testing
,
an alter table statement can be 2 to 3 times faster without trigger comparin
g
to disabled triggers.
Thanks for any help,
PeterHi Peter
Disabling the triggers is the best you can do unless you drop and re-create
them.
Out of interest Is there alot of code in these triggers or are they nested?
John
"Peter" wrote:

> Hi,
> I wonder whether there is a way to temporarily hide triggers from SQL Serv
er
> 2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want
to
> do this is because even though I disable triggers, SQL Server will take ti
mes
> to evaluate whether the trigger is disabled or not. Based on my own testi
ng,
> an alter table statement can be 2 to 3 times faster without trigger compar
ing
> to disabled triggers.
>
> Thanks for any help,
> Peter|||Hi Peter
Disabling the triggers is the best you can do unless you drop and re-create
them.
Out of interest Is there alot of code in these triggers or are they nested?
John
"Peter" wrote:

> Hi,
> I wonder whether there is a way to temporarily hide triggers from SQL Serv
er
> 2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want
to
> do this is because even though I disable triggers, SQL Server will take ti
mes
> to evaluate whether the trigger is disabled or not. Based on my own testi
ng,
> an alter table statement can be 2 to 3 times faster without trigger compar
ing
> to disabled triggers.
>
> Thanks for any help,
> Peter

How to hide triggers from SQL Server?

Hi,
I wonder whether there is a way to temporarily hide triggers from SQL Server
2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want to
do this is because even though I disable triggers, SQL Server will take times
to evaluate whether the trigger is disabled or not. Based on my own testing,
an alter table statement can be 2 to 3 times faster without trigger comparing
to disabled triggers.
Thanks for any help,
PeterHi Peter
Disabling the triggers is the best you can do unless you drop and re-create
them.
Out of interest Is there alot of code in these triggers or are they nested?
John
"Peter" wrote:
> Hi,
> I wonder whether there is a way to temporarily hide triggers from SQL Server
> 2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want to
> do this is because even though I disable triggers, SQL Server will take times
> to evaluate whether the trigger is disabled or not. Based on my own testing,
> an alter table statement can be 2 to 3 times faster without trigger comparing
> to disabled triggers.
>
> Thanks for any help,
> Peter