Instead of triggers - 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

General SQL Server Forums

New to SQL Server Programming

Instead of triggers

Author

Topic

Fred_Scuttle
Starting Member

13 Posts

Posted - 2005-11-14 : 12:06:40

Hi I work with a product called GoldMine which has it's data on SQL server.
When someone deletes a record, it's gone forever.
I would like to be able to make that record just move to another SQL database.
I would like to put an instead of delete trigger on 2 tables in my database.
So that when user1 deletes a record in GoldMine, Then Goldmine sends its DELETE query to tables 1 and 2, they then don't get deleted, instead they get inserted into Tables 1 and 2 on my new "Deletes" database.
Can anyone help with the basic syntax.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-11-14 : 12:09:06

NR has some great examples here:

[url]http://www.mindsdoor.net/SQLTriggers/Triggers_1_A_beginners_guide.html[/url]

Nathan Skerl

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-14 : 14:30:47

You should be very, very careful about making a change like this to a vendor provided database. You probably do not know all the problems this could cause without having a deep understanding of the Goldmine application.

Also, I think you would want to use a AFTER trigger, not an INSTEAD OF trigger. If you use an INSTEAD OF trigger, the data will not be deleted from your Goldmine database.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2005-11-14 : 21:54:07

~~INSTEAD OF~~ AFTER trigger should be pretty safe. The "deleted" data can be copied to the "Deletes" database, and the actual record will have been physically deleted in the normal way, so Goldmine should function as normal.

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-14 : 22:19:18

I believe that the action of in INSTEAD OF trigger means that the only thing that happens is what is specified in trigger, so if you do not do the delete in the trigger, it doeasn't get done.

From BOL "Designing Triggers" topic:
"INSTEAD OF triggers are executed in place of the usual triggering action."

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2005-11-14 : 23:08:38

Sorry, finger trouble ... you are quite right MVJ.

Kristen

Fred_Scuttle
Starting Member

13 Posts

Posted - 2005-11-14 : 23:47:24

Thank you all for your input.
The only question is, if I do an after trigger won't it be too late to copy that data because it will have been deleted? Or am i missing something there?

Regards

Fred

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-14 : 23:56:35

It is not too late. The deleted data is still available in deleted table

from BOL

quote:
"The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common."

[KH]

Fred_Scuttle
Starting Member

13 Posts

Posted - 2005-11-15 : 02:18:12

Thanks again,

I have found out that the AFTER TRIGGER does not allow the copying of TEXT fields. One of my fields is called NOTES and is a text field. I don't want to lose it.

Can I do an INSTEAD OF trigger that copies out the whole data and then does the delete. (i.e perform 2 actions)
if so what is the syntax?

Also does the table I am copying into need to be in the same database as the tables being deleted from?

Thanks

Fred

Kristen
Test

22859 Posts

Posted - 2005-11-15 : 06:11:09

"Can I do an INSTEAD OF trigger that copies out the whole data and then does the delete"

Rest, but because it completely replaces the normal behaviour there is a risk that it interferes with the behaviour of the application (or a future upgrade)

Code from a process we use to auto-generate Triggers (which I've edited a bit!):

Globally Find & replace:

MyTrigger
MyTable
MyPK
MyArchiveTable


PRINT 'Create trigger MyTrigger'
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'MyTrigger' AND type = 'TR')
	DROP TRIGGER dbo.MyTrigger
GO
CREATE TRIGGER dbo.MyTrigger
ON dbo.MyTable
/* WITH ENCRYPTION */
INSTEAD OF DELETE
AS
/*
 * MyTrigger	Trigger for delete of records in MyTable
 *
 */
SET NOCOUNT ON
SET XACT_ABORT ON 
SET ARITHABORT ON 

	DELETE	T
	FROM	deleted AS D
		JOIN dbo.MyTable AS T
			 ON T.MyPK = D.MyPK

	INSERT dbo.MyArchiveTable
	SELECT	D.*
	FROM	deleted D

--================== MyTrigger ==================--
GO
PRINT 'Create trigger MyTrigger - DONE'
GO

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources