tigger advice - 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

tigger advice

Author

Topic

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-01-29 : 16:17:56

Greetings All,

I am coming from a foxpro arena into SQL.
I am currently working on a trigger that
performs a simple copy. Example

copy record to another table if value of field = 1,
then delete orginal record.

CREATE TRIGGER arcit
AFTER UPDATE ON ACTIVE

INSERT
INTO Archive(grid, name)
SELECT *
FROM ACTIVE
WHERE arc = 1
DELETE FROM ACTIVE
WHERE arc = 1
GO

I get the following error

Msg 102, Level 15, State 1, Procedure arcit, Line 2
Incorrect syntax near 'AFTER'.

Also second question with the "INTO Archive(grid, name)"
is their not some global grab all fields I can use here ?

Anyhelp would be great..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-29 : 17:44:13

The error is just because of incorrect syntax. But you should read up on Sql Triggers in Books Online to understand [Deleted] and [Inserted] tables. The way I coded it may not be the way you want it but it is only doing the copy to archive and delete from active if the [arc] value has been updated to 1 (from something other than 1).


CREATE TRIGGER arcit ON [ACTIVE] AFTER UPDATE 
as
begin
	begin tran

	--insert to Archive table only when arc has been updated to "1"
	INSERT INTO Archive(grid, name)
	SELECT i.grid, i.name
	from	Inserted i 
	inner join deleted d
		on d.<PRIMARYKEYcolsFromActive> = i.<PRIMARYKEYcolsFromActive>
	inner join [Active] a
		on a.<PRIMARYKEYcolsFromActive> = i.<PRIMARYKEYcolsFromActive>
	WHERE i.arc = 1
	and   isNull(d.arc,0) != 1

	--Delete from Active table only when arc has been updated to "1"
	DELETE FROM a
	from	Inserted i 
	inner join deleted d
		on d.<PRIMARYKEYcolsFromActive> = i.<PRIMARYKEYcolsFromActive>
	inner join [Active] a
		on a.<PRIMARYKEYcolsFromActive> = i.<PRIMARYKEYcolsFromActive>
	WHERE i.arc = 1
	and   isNull(d.arc,0) != 1

	commit tran
end
GO

Be One with the Optimizer
TG

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-01-29 : 18:27:28

Thank You, this gives me a base start learning from..

I do have another question, Why does this trigger
require an inner-join ?

In Foxpro a simple "use x: table and copy from command"
would do the trick. If both table A and table B are in the
same Database it would seem to me you could apply the same
logic in SQL.

Any insight would be great.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-29 : 23:35:41

The inner join with INSERTED,DELETED tables is to identify the currently updated records.These tables will have a copy each of every record that got updated with one holding old values and other holding new values.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-30 : 00:00:20

The best advice is to avoid using triggers, and perform the logic in a stored procedure. It is much easier to control what you want to do and much easier to test and debug.

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-30 : 08:33:34

quote:
Originally posted by Michael Valentine Jones

The best advice is to avoid using triggers, and perform the logic in a stored procedure. It is much easier to control what you want to do and much easier to test and debug.

CODO ERGO SUM

I concur whole heartedly! I have found that the developers rarely appreciate the implications of lumping more sql statements in with the transaction as the "user transaction".

Also I predict you will have some very confused users when the row that they were just updating dissapears out from under them.

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-30 : 20:27:41

quote:
tigger advice

Ask Winnie

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources