trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

trigger

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-08 : 23:58:15

can i create a trigger in database1 but the records will be inserted in table_audit in database2?

if there is,then how?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-09 : 00:48:47

ya its possible.

just write a insert

insert into database2.table_audit (..)values(..)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-09 : 00:54:18

i have this code but there is an error and the error is:

Msg 8197, Level 16, State 4, Procedure TRG_CustomerDetails, Line 1
Object 'AUDIT_DATABASE.Audit_CustomerDetails' does not exist or is invalid for this operation.


create trigger TRG_CustomerDetails on AUDIT_DATABASE.Audit_CustomerDetails
for insert, update, delete
as
declare @inserted int,
@deleted int,
@type char(6)
select @inserted = count(*) from inserted
select @deleted = count(*) from deleted
set @type = case
when (@inserted > 0) and (@deleted = 0)then 'insert'
when (@inserted > 0) and (@deleted > 0)then 'update'
when (@inserted = 0) and (@deleted > 0)then 'delete'
end
if (@type IN ('insert','update'))
insert into AUDIT_DATABASE.Audit_CustomerDetails
select Audit_ID, current_timestamp, @type , Host_Name()
from inserted
else
insert into AUDIT_DATABASE.Audit_CustomerDetails
select Audit_ID, current_timestamp, @type , Host_Name()
from deleted
return

ravis
Starting Member

7 Posts

Posted - 2009-06-09 : 00:56:50

Yes we can do using trigger.

create trigger triggername on tablename fro insert, delete,update
as

insert into database2..table_audit ( )
values ( )

Ravi

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-09 : 01:10:18

ok thanks for helping..
the table that put in the trigger is wrong.. now it's working..

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-09 : 01:13:55

what if the trigger is the database2 and the table that i want to audit is in database1?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-09 : 01:15:37

quote:
Originally posted by chriztoph

what if the trigger is the database2 and the table that i want to audit is in database1?

just interchange the query

insert into database2.table_audit (..)values(..)

as

insert into database1.table_audit (..)values(..)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources