Trigger help! - 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

Trigger help!

Author

Topic

jonny1990
Starting Member

6 Posts

Posted - 2011-05-10 : 11:50:22

Hi guys i think there called DLL triggers and i need help with them.

** I AM VERY NEW WITH SQL SO DONT FLAME PLEASE :) **

Lets say i have a table called Employee and Employee1

When a specific date such as 10.05.2011 at a specific time lets say 15:00pm i want all the data to be transfered from employee to employee1 and new data inserted into employee.

How could i go about doing this? any advice would be much appreciated!!

I've heard of using Triggers or Transactions? other advice or help would be great!

Thanks j

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-10 : 11:59:04

Instead of a trigger, you need to schedule a job that fires off a stored procedure that moves the data around. The job can be scheduled to run at any time.

Jim

Everyday I learn something that somebody else already knew

jonny1990
Starting Member

6 Posts

Posted - 2011-05-10 : 12:02:03

ok, is there any other ways, as i would have no idea how to implement this? and is it easy enough to do?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-10 : 12:23:20

It's pretty easy, in fact it's easier to do than to describe:

1. In Management Studio, expand the SQL Server Agent in the treeview (Object Explorer)
2. Right-click the Jobs folder, choose New Job
3. On the General tab, fill out the Name for the job (make sure there's no job with that name already)
4. On the Steps table, click the New button. Enter a Name for the step.
5. Make the Type "Transact-SQL script" if it's not already.
6. Set the proper database and enter your SQL statement or stored procedure call in the Command window (e.g. EXEC myProcedure)
7. Click OK, get back to New Job window, choose Schedule.
8. Set the schedule as you described. Choose One-Time if you only want it to run once.
9. Click OK on all windows until you get back to regular Management Studio. You'll see the new job in the job list.

If you have any doubt or feel you messed up, right-click the job and delete it. You can try any number of practice jobs before you commit to the real one.

jonny1990
Starting Member

6 Posts

Posted - 2011-05-10 : 13:15:37

Thanks rob, have taken this way into consideration.

Is there another way lets say in psudocode that i could do this.
tablea
tableb
temptable

select * from tablea
insert into a temptable
remove everything out of table b
insert all of temptable data into table b.

is this possible?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-10 : 13:37:25

Sure, something like:

SELECT * INTO tempTable FROM tableA
TRUNCATE TABLE tableB
INSERT INTO tableB SELECT * FROM tempTable

Although it's easier to do:

TRUNCATE TABLE tableB
INSERT INTO tableB SELECT * FROM tableA

jonny1990
Starting Member

6 Posts

Posted - 2011-05-10 : 17:49:53

ok mate, thanks i will test this in the morning let you know the results.

jonny1990
Starting Member

6 Posts

Posted - 2011-05-10 : 18:14:41

Thanks for that mate, tested it and it works! ok i need to adapt it now, to ... if table a has 15 results of data in, i want to take only the first 10?

TEST
TEST2

TRUNCATE TABLE TEST1 -- remove everything from TEST1
INSERT INTO TEST1 SELECT * FROM TEST -- put info into TEST1
TRUNCATE TABLE TEST -- deleting everything from test

SELECT * FROM TEST1 -- checking to see if deleted

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-10 : 18:33:27

Tell us exactly what your requirements are, rather than piece-mealing us on a need-to-know basis. The new requirements you present may have changed how we would have answered the previous request. Also, what determines the TOP 10?

Jim

Everyday I learn something that somebody else already knew

jonny1990
Starting Member

6 Posts

Posted - 2011-05-11 : 11:09:13

The top ten results will be inserted manually, obviously if this was a real life system i would be using some sort of counter which would they determine the top ten.

but for now, i will be inserting new top 10 data.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-11 : 11:36:11

There has to be some way, based on the columns in the table, to identify the top 10. SQL has no concept of ordering in a table, so you can't say insert order.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources