|
|
| Adarsh 2006-02-28, 8:23 pm |
| 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.
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| 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.
>
| |
| Adarsh 2006-02-28, 8:23 pm |
| 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..:)
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| 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..:)
>
| |
| Adarsh 2006-02-28, 8:23 pm |
| Can u please tell me why we should avoid using Triggers?
Thanks.
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| 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.
>
| |
| Adarsh 2006-02-28, 8:23 pm |
| ok Thanks a lot Uri... :)... It helped me a lot
| |
| Adarsh 2006-02-28, 8:23 pm |
| 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
|
|
|
|