quote:

---

Originally posted by Studevs

Hi All,

Im verry new in this and hope you guys help me.
i have to tables:
- articles
- customers

i want to write a trigger: when inserting a new record to the table 'customers', a new record most be added into the 'articles' too. the new record in the 'articles' most have the new added 'customernr' and '0' .

Thanks

---

CREATE TRIGGER YourTrigger
ON customers
AFTER INSERT
AS
BEGIN
INSERT INTO article (fields..)
SELECT customerno,'0',...
FROM INSERTED
END

quote:

---

Originally posted by chriztoph

is it possible to create a trigger for multiple table?if yes,how?

---

quote:

---

Originally posted by chriztoph

how about creating a trigger with separate database?

example:

sampletable on sampledatabase

and the audit will be saved in

audit_sampletable on auditsampledatabase

---

CREATE trigger [TRG_CustomerDetails] on [dbo].[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_dbECI.CustomerDetails_Audit
select Customer_ID, current_timestamp, @type , Host_Name()
from inserted
else
insert into Audit_dbECI.CustomerDetails_Audit
select Customer_ID, current_timestamp, @type , Host_Name()
from deleted
return

quote:

---

Originally posted by chriztoph

how?

can you give me sample trigger for that?

i have a trigger here for insert, update and delete

on database 'dbECI' with a table 'CustomerDetails'

and 'Audit_dbECI.CustomerDetails_Audit' here is the database and table

CREATE trigger [TRG_CustomerDetails] on [dbo].[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_dbECI.CustomerDetails_Audit
select Customer_ID, current_timestamp, @type , Host_Name()
from inserted
else
insert into Audit_dbECI.CustomerDetails_Audit
select Customer_ID, current_timestamp, @type , Host_Name()
from deleted
return

but every time to execute an Insert,update and delete in table CustomerDetails i get this error


Msg 208, Level 16, State 1, Procedure TRG_CustomerDetails, Line 16
Invalid object name 'Audit_dbECI.CustomerDetails_Audit'.

---

quote:

---

databasename.schemaname.tablename
maybe: Audit_dbECI..CustomerDetails_Audit
or : Audit_dbECI.dbo.CustomerDetails_Audit

---