Trigger or SP

Hi,
I have a situation in which a  record in inserted into a table say
table1 when any record is inserted in any table in the whole
application. Is it better to use trigger or to write the insert query
in the SP for every table itself.

I'm not sure inderstood you. What is the purpose?

CREATE TRIGGER TR__Update_MyTable
ON MyTable FOR UPDATE
AS
UPDATE MyTable
SET UpdatedBy = SUSER_SNAME(),
LastUpdatedOn = GETDATE()
WHERE EXISTS
(
SELECT *
FROM inserted
WHERE inserted.PK = MyTable.PK
)



"Adarsh" <shahadarsh@gmail.com> wrote in message
news:1141031175.213071.75040@i40g2000cwc.googlegroups.com...
> Hi,
>     I have a situation in which a  record in inserted into a table say
> table1 when any record is inserted in any table in the whole
> application. Is it better to use trigger or to write the insert query
> in the SP for every table itself.
>

Hi Uri,
The purpose is to keep a track of all the new records added in any
table and storing the primary key in 1 common table. So my question is
that..... Is it better to use trigger in this situation (to insert
record in the common table) or to put the insert query for the common
table directly in the insert SP 's of the tables?
My question is coz its believed that use of trigger should be
avoided...

Please tell if u need any further clarification.
Thanks a lot..:)

Hi
SP


"Adarsh" <shahadarsh@gmail.com> wrote in message
news:1141032494.303581.312640@t39g2000cwt.googlegroups.com...
> Hi Uri,
>   The purpose is to keep a track of all the new records added in any
> table and storing the primary key in 1 common table. So my question is
> that..... Is it better to use trigger in this situation (to insert
> record in the common table) or to put the insert query for the common
> table directly in the insert SP 's of the tables?
> My question is coz its believed that use of trigger should be
> avoided...
>
> Please tell if u need any further clarification.
> Thanks a lot..:)
>

Can u please tell me why we should avoid using Triggers?

Thanks.

Well, it depends on how often youb tables get updated/inserted and how many
rows are affected?
If you do the inserting within SP it is really easy to put insert  a new row
into an audit table just after your inserting.

BTW , in SQL Server 2005 there is a new feature OUTPUT clause  to allow
audit tables

create table mytable (col1 int identity not null primary key, col2  int not
null unique )
create table #audit (col1  int not null, col2   int not null)

insert into mytable (col2)
output inserted.col1  , inserted.col2    into #audit
select o.object_id from sys.objects as o

select * from #audit

go




"Adarsh" <shahadarsh@gmail.com> wrote in message
news:1141043938.297582.177890@z34g2000cwc.googlegroups.com...
> Can u please tell me why we should avoid using Triggers?
>
> Thanks.
>

ok Thanks a lot Uri... :)... It helped me a lot

Hi Uri,
When I add WHERE CLAUSE IN UPDATE query the OUTPUT doesn't work.

This code works :
DECLARE @OldProposal TABLE
(ProposalDesc varchar(200))

UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @OldProposal
SELECT * FROM @OldProposal

and this code doesn't work:

DECLARE @OldProposal TABLE
(ProposalDesc varchar(200))

UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @OldProposal WHERE ProposalID=9;
SELECT * FROM @OldProposal