Trigger when a column is deleted

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 when a column is deleted

Author

Topic

yashk
Starting Member

10 Posts

Posted - 2009-10-16 : 13:34:32

I have a scenario where i have two tables Schema1.DIMCNAM
and Schema2.DIMCNAM.
Schema1.DIMCNAM and Schema2.DIMCNAM have same columns except Schema2.DIMCNAM has a extra column named INSERT_DT.

Whenever a column gets deleted trigger should be fired and the deleted column values along with record should go to Schema2.DIMCNAM and Deleted column should be removed from Schema1.DIMCNAM.

How can i write a trigger for this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-16 : 14:07:30

What do you mean by "deleted column"?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

yashk
Starting Member

10 Posts

Posted - 2009-10-16 : 14:32:17

Please see my specification

yashk
Starting Member

10 Posts

Posted - 2009-10-16 : 14:42:07

This is my specificaiton

There are 2 triggers that need to be setup to remove certain columns form the GDW when a delete happens in OMS.

Triggered Table
Schema1.DIMCNAM

Where to be inserted
Schema2.DIMCNAM

We need a delete trigger to capture when a delete happens on the table DIMCNAM. When a delete happens we need to insert a record into Schema2.DIMCNAM

DIMCNAM
Original Value-----> Inserted Value

column1 --------> column1
column2------> column2
column3 -------> 'P'
column4 -------> column4
column5 -------> column5
column6 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFEQ
column7 --------> if column6 = 'A' or 'B' then NULL ELSE FHAERS
column8 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFES
column9 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFET
column10 --------> if column6 = 'A' or 'B' then NULL ELSE FHAFES
column11 --------> if column6 = 'A' or "B' then NULL ELSE FHAFEU
column12 --------> column12
........................................
Column29-------->column29

This is the Specification given to me.
According to the specification can anyone suggest what i have to write?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-16 : 14:59:18

something like this...Whenever a "row" is deleted from DIMCNAM, that row will be inserted into the other table.

CREATE TRIGGER <triggername>
ON Schema1.DIMCNAM
AFTER DELETE
AS 
   INSERT INTO Schema2.DIMCNAM
   SELECT <whateverfieldsneeded>
   FROM Deleted 
GO

yashk
Starting Member

10 Posts

Posted - 2009-10-16 : 15:08:47

Thanks for the reply
Are you sure that 'Whenever a row is deleted from DIMCNAM, that row should be inserted into other table'
What if a value is deleted in the DIMCNAM table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-16 : 15:13:21

Duplicate topic, locking thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=134491

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources