Need help with 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)

Need help with trigger

Author

Topic

yashk
Starting Member

10 Posts

Posted - 2009-10-15 : 15:55:32

I have a situation where i have 2 tables.
If any value is deleted is table 1 then it should be detected and the deleted value should go to table 2.
I am saying just a value not a row or column.
So should i write a trigger for delete or update?

tkizer
Almighty SQL Goddess

38200 Posts

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

I wouldn't use a trigger. I'd instead put the code in a stored procedure to handle both tables.

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 : 08:06:59

Thanks for your reply.

As you said it would be better to write code in stored procedure.
Unfortunately i don't have an option to do so. I have to write a trigger( I am told to do so by my head)

If any value is deleted in Table 1 that value should be deleted in table 1 and the deleted value should go to table 2.
Is it possible to write a trigger for this situation.
Even if we write a trigger how can we keep track of that deleted value in table2. If only value goes to table 2 then how can we know to which row that value belongs to.

asgast
Posting Yak Master

149 Posts

Posted - 2009-10-16 : 09:31:55

You can't delete a value in a table, only a row
You can set a value to '' or zero depending on a column type.

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

yashk
Starting Member

10 Posts

Posted - 2009-10-16 : 09:42:51

I think it is possible to delete a value
If someone who have access to table deletes a value just by right click on the value and delete (not through code) then the value will be deleted.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-16 : 10:41:48

That is not a delete in terms of sql!
It is an update of a column which is replacing the old value 'bla' by nothing...

No, you're never too old to Yak'n'Roll if you're too young to die.

yashk
Starting Member

10 Posts

Posted - 2009-10-16 : 10:52:53

Yes it is an update in SQL. You are right.

My task is to write a trigger so that whenever a column is deleted in Schema1.DIMCNAM( My table name) then that values should be inserted into Schema2.DIMCNAM and should be deleted in Schema1.DIMCNAM

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-16 : 11:12:21

So think concrete to the end.
I assume there are more than one column with different data types in Schema1.DIMCNAM.
So Schema2.DIMCNAM should be clone of Schema1.DIMCNAM plus any new columns to take information about datetime of changes and mybe old AND new values...

Describe exactly what you want to do please because there are too many open questions to advice for a good working trigger.

No, you're never too old to Yak'n'Roll if you're too young to die.

yashk
Starting Member

10 Posts

Posted - 2009-10-16 : 11:17:21

I have two tables
Schema1.DIMCNAM and Schema2.DINCNAM
Both have same column names excpet Schema2.DIMCNAM have one extra INSERT_DT.

My task is to write a trigger so that whenever a column or values in a column are deleted in Schema1.DIMCNAM( My table name) then that values should be inserted into Schema2.DIMCNAM and should be deleted in Schema1.DIMCNAM

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-16 : 11:29:44

So an update happens to Schema1.DIMCNAM.

One column has changed from value 'webfred' to value 'yashk' so this is not a delete in your terms and there is nothing to do for the trigger?

Another update happens to Schema1.DIMCNAM.
One column has changed from value 'yak' to value '' or NULL and all other columns are unchanged.
So what is to do for the trigger? Insert 'yak' into a new row (Schema2) in the corresponding column with actual datetime in insert_dt and all other columns (Schema2) are NULL?

What if a ROW gets deleted (really deleted)?

No, you're never too old to Yak'n'Roll if you're too young to die.

yashk
Starting Member

10 Posts

Posted - 2009-10-16 : 11:33:10

Let me give you my technical spec so that it will be clear for you.

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?

SQLTony2008
Starting Member

1 Post

Posted - 2009-10-16 : 12:20:52

quote:
Originally posted by yashk

I have a situation where i have 2 tables.
If any value is deleted is table 1 then it should be detected and the deleted value should go to table 2.
I am saying just a value not a row or column.
So should i write a trigger for delete or update?

You should write an update trigger.

TK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources