triggers on update/delete/insert

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

SQL Server Administration (2005)

triggers on update/delete/insert

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-11-15 : 23:32:04

Dear All,

For the purpose of duplication (this is intentional)i have created a duplicate table inside database called Employee and Employee_rep.

Employee is the base table whatever the transaction happened on it,should be applied for Employee_rep as well, in terms of INSERT/UPDATE/DELETE.

I have tried to create triggers for this purpose for insert

CREATE TRIGGER trgAfterInsert_employee ON [dbo].employee
FOR INSERT
AS
insert into EMployee_rep(
NameGiven,
NameFamily,
WID,
WindowsUserName,
Email,
Country_ID from inserted
Go

i need similar trigger for DELETE and update... whenevr the record has been deleted from base table in the duplicate table also it should delete and its same for update.

Could you pls help.

Sachin.Nand

2937 Posts

Posted - 2011-11-16 : 03:29:16

Set up a replication for the table.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 04:00:47

see

http://www.informit.com/articles/article.aspx?p=599700

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-11-16 : 05:44:46

I don't want to use the replication, i want to do it in only trigger

Thanks,
Gangadhara MS
SQL Developer and DBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 05:49:19

quote:
Originally posted by gangadhara.ms

I don't want to use the replication, i want to do it in only trigger

Thanks,
Gangadhara MS
SQL Developer and DBA

Why so?
Replication suits your scenario most. didnt understand why you want to do it hard way

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-11-16 : 06:06:42

quote:
Originally posted by gangadhara.ms

I don't want to use the replication, i want to do it in only trigger

Thanks,
Gangadhara MS
SQL Developer and DBA

Are you using an Express edition that you cannot use replication ?

PBUH

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-16 : 10:58:39

No way would I set up replication for a single table in the same database. Can it be done? Sure. But in this case, triggers are likely the better choice.

I don't understand why the OP wants 2 copies of the exact same table in the same database though.

gangadhara.ms, you already have the syntax for the insert trigger. for delete and update triggers, you just need to join to the INSERTED (for updates) and DELETED (for deletes) virtual tables, joining on the primary key and then either update/delete against the target table.

Why have 2 copies in same db though?

Kristen
Test

22859 Posts

Posted - 2011-11-16 : 13:41:10

quote:
Originally posted by russell

gangadhara.ms, you already have the syntax for the insert trigger.

Although the syntax shown won't work as it stands - its not clear to me if that was just an "example", or if the O/P's question is partly to ask the correct syntax.

Sachin.Nand

2937 Posts

Posted - 2011-11-17 : 05:27:56

Sorry...I totally missed the part that the OP wanted a copy of the table on the same database so replication wont work in the scenario as a same database cannot act as a publisher as well as a subscriber.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources