Showing posts with label whenever. Show all posts
Showing posts with label whenever. Show all posts

Thursday, February 16, 2012

being more specific

Hi all,

i want to write a trigger which returns a row to the application whenever a row is inserted into a table.

E.g.

When row R1 is inserted into a table T1.

the trigger should return the R1 to the application.

Wanna be coding monkey...

hi chaman,

its a bad practive for a trigger to be recordset returning.

trigger are best used for sending the result somewhere in the database

or for task such as maintaining the database consistency after an insert.

what are you going to do with it. can you specify what's your scenario so we can provide more help

if this is really what you need. you should write a stored procedure instead

regards,

joey

|||

Chaman:

I agree completely with Joey's position -- returning result sets from a trigger is in general a very bad idea. Also, there are other things that you need to consider should you continue with the result-set-from-a-trigger approach. It seems to me from your post that you have not considered impact whenever a single insert statement inserts multiple records into the table.

Are you motivated to return the row by any chance because you are using an identity column?


Dave

|||

Yes.. I also accept both Joeydj & Mugambo's comment..

If you want to read all about SQL Server Trigger visit this MSDN magazine Data Point article http://msdn.microsoft.com/msdnmag/issues/03/12/DataPoints/default.aspx#S4

http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/

if you don;t have time to read these article read the summary of these article on

http://weblogs.asp.net/akinney/archive/2003/11/14/37509.aspx

But really we don't know the purpose of Chaman's need...I will give the solution but before doing this read all these article and choose your decesion.( i am not ready to say its not possible at all in sql server )

on your trigger use the following statement

select * From Inserted (note: this statement only work on Trigger)

|||

Hi all,

thanks for your help,

well as i am new in this SQL let me be clear on my requirements..

1. we have a table T1, when a new row R is added in T1,

i have a service who triggers an action (say beep) depending on the entities in R,

i want a trigger who will send the data in R of T1 to that service.

Now, question 1 - is it possible to write sucha trigger?

2. if yes, how? how can a table return a value to a service.

Regards,

chaman.

|||How is this service listening for the prompts?|||

i think you need to create a service that listens or monitor the event in the db.

any way if your into service orinted design

it is not definetely the trigger that you will have to use.

you might as well consider "sql server notification services" and/or the service broker

Sunday, February 12, 2012

Beginner need help ... with triger?

I have a table that records data and I want to be able to copy a few of the feilds to another table in a second database whenever the data is inserted or updated. I assume the way to do that is thru the use of a trigger, but have no clue how to begin.

For example:

db #1 Table User has fields;
id, name, address, zip, password, date

db #2 table n_user has fields;
id, name, password

Hope thats enough information to get some assistance...use [Your active DB]
GO
create trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([id], [name], [password])
select [id], [name], [password]
from inserted
end|||Thanks for you help.
I made the trigger but get this error on update of one field value.

Cannot insert explicit value for identity column in table 'FORUM_MEMBERS' when IDENTITY_INSERT is set to OFF.

Any clue??|||Difference between history and active table is difference between picture and movie.
You cannot use the same PK in both tables.

In [Your history DB].dbo.[n_user] table:
1,Add [oldid] column with datatype of [user].[id] column, PK will be on [n_user].[id] column.
2,Also consider using larger datatype on [n_user].[id] column, if [user] table is modified frequently.

3,Modify trigger:

use [Your active DB]
GO
alter trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
select [id], [name], [password]
from inserted
end
GO
delete [Your history DB].dbo.[n_user]
declare @.x int
update dbo.[user] set @.x=1

4...This will help sometimes
Adding FingerPrint timestamp column NOT NULL
Adding CreatedDate datetime column NOT NULL with default getdate().

5,Securing history table:

use [Your history DB]
GO
Deny insert,update,delete,references on dbo.[n_user] to public|||I see parts in the code for insert and delete... what about update?

Again... Thank you very much for your help.|||/*
You wrote:
"I have a table that records data and I want to be able to copy a few of the feilds to another table in a second database
WHENEVER THE DATA IS INSERTED OR UPDATED."
So you did not specify DELETED info.
*/

/* COMMENTED CODE */
--Switching to your active DB
use [Your active DB]
GO
--Creates trigger FOR INSERT,UPDATE on dbo.[user]
--This trigger uses INSERTED table of NEW VALUES for BOTH INSERT AND UPDATE
--( Look at "inserted tables" topic in BOL )
if object_id('TR_User(,I,U,)') is not null drop trigger [TR_User(,I,U,)]
GO
create trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
select [id], [name], [password]
from inserted
end
GO
--Deleting test rows in HISTORY
delete [Your history DB].dbo.[n_user]
--Filling history table with previosly inserted data (prehistoric)
declare @.x int
update dbo.[user] set @.x=1|||Ok... looks like the insert new member fires off the trigger just fine. Both tables are updated correctly. :)

However, when a user tries to update their profile... They recieve an sql error stating a primary key violation.

Here is the exact trigger that causes that error.

==============

CREATE trigger [TR_Players(,I,U,)]
on dbo.[players]
for insert,update
as begin
insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end

=============

Now I went ahead and edited the trigger to be this,

=============

CREATE trigger [TR_Players(,I,)]
on dbo.[players]
for Insert
as begin
Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end

=============

As you might expect... this fires off correctly and both tables get their new information.

Now the question is how do I get the update trigger to update table 2 (FORUM_MEMBERS) without attempting to add another row with the same information, thus violating the pk constraint.

I tried to add a second trigger to the same table just for updates like such.

=============

CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end

=============

Too bad that gave me the exact same error. Also, syntax wise... im kind of confused why I had to use "inserted" and not "updated" to get successful syntax checking? Does the temp table updated not exist with triggers? Can I not run two seperate triggers against the same table?

Oh and as an FYI. I did not want delete syntax... I was just commenting that in your post i saw the keyword delete?

Like always... thanks very much for your time to deal with my issues..|||CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
update fm set
fm.M_name = i.[PEmail]
, fm.M_username = i.[PEmail]
, fm.M_password = i.[Ppassword]
, fm.M_email = i.[PEmail]
, fm.M_quote = i.[pcomments]
from [Forum].dbo.[FORUM_MEMBERS] fm
join inserted i
on fm.M_name=i.[PEmail] --PK join - verify
end

--for more See http://dbforums.com/showthread.php?threadid=640545|||CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
update fm set
fm.M_name = i.[PEmail]
, fm.M_username = i.[PEmail]
, fm.M_password = i.[Ppassword]
, fm.M_email = i.[PEmail]
, fm.M_quote = i.[pcomments]
from [Forum].dbo.[FORUM_MEMBERS] fm
join inserted i
on fm.M_name=i.[PEmail] --PK join - verify
end

--for more See http://dbforums.com/showthread.php?threadid=640545|||You are like a God to me! Works great. Thank You Thank you! :)|||It seems that I need to make another trigger to pass changed passwords back to the original table. I attempted to modify the previous trigger to work in reverse. But no suck luck. I could use alittle assistance here.

I want the trigger to take the M_password field from the forum_members table (when its updated) and update the other database
table players with the new password. Here is what I came up with...

---------------
CREATE trigger [TR_pw(,U,)]
on [Forum].dbo.[FORUM_MEMBERS]
for Update
as begin
update pl set
pl.Ppassword = i.[M_password]
from dbo.[players] pl
join inserted i
on pl.pemail=i.m_name --PK Join - verify
end
---------------

Thanks.|||Your triggers are probably chaining, learn more about nesting
http://dbforums.com/showthread.php?threadid=640545|||you know your prolly correct... cuz the error message im recieving says something about exceeding the number of database connections.

Ive read the post you refered me to... but am still not sure what it said. Sorry... im new to this. Can I assume that my trigger is formed correctly? But I need to some how limit its ability to fire from an update by another trigger?|||Stike that...
I went to BOL and found how to turn off recursive triggers... I turned them off and it looks like its working. Is there any effect on regular system operation due to this trigger setting?

Thanks for your help.|||You can put "if trigger_nestlevel(@.@.procid)>1 return" in the beginning of your data modifying trigger. Checking-only triggers stay active in the trigger chain. Also even with switching nested triggers off I cannot make Ver1 (table mirroring) working. Also when you are using recursive algorithm for trigger, you must have recursive triggers on.
In most cases you do not need it.

--Ver1
create table A(X int)
create table B(X int)
GO
create trigger tiA on A for insert as
insert B select * from inserted
GO
create trigger tiB on B for insert as
insert A select * from inserted
GO
insert A values (1)
GO
drop table A
drop table B

--Ver2
create table A(X int)
create table B(X int)
GO
create trigger tiA on A for insert as
if trigger_nestlevel(@.@.procid)>1 return
insert B select * from inserted
GO
create trigger tiB on B for insert as
if trigger_nestlevel(@.@.procid)>1 return
insert A select * from inserted
GO
insert A values (1)
GO
drop table A
drop table B

Good luck !