Triggers with insert or update (With Backup)

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 2008 Forums

Transact-SQL (2008)

Triggers with insert or update (With Backup)

Author

Topic

M.Hazem.1987
Starting Member

5 Posts

Posted - 2010-08-31 : 06:55:04

Hi,
I Have Table1...
When someone insert,update or delete a row in this table.....I want a trigger that create backup for my database.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-31 : 06:57:50

So what did you try?
Where are the problems?
Or do you want us to do your complete work?

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

M.Hazem.1987
Starting Member

5 Posts

Posted - 2010-08-31 : 07:03:08

ok...I try this

create trigger AnyName on Table1
for insert,update,delete
as
begin
BACKUP DATABASE [MYDBName] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MYDBName.bak
end

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-31 : 07:18:57

Hang on...
Any time someone inserts or modifies a row, you want to take a full database backup? Unless this table is only modified occasionally, this may not be a very good idea at all...

What's the idea behind this? There's likely a better way.

--
Gail Shaw
SQL Server MVP

M.Hazem.1987
Starting Member

5 Posts

Posted - 2010-08-31 : 07:31:15

quote:
Originally posted by GilaMonster

Hang on...
Any time someone inserts or modifies a row, you want to take a full database backup? Unless this table is only modified occasionally, this may not be a very good idea at all...

What's the idea behind this? There's likely a better way.

--
Gail Shaw
SQL Server MVP

Yes....
so I want to take a full database backup when this table is modified.
but my code lead to error

quote:

There's likely a better way.

what do you mean ?like what ?

M.Hazem.1987
Starting Member

5 Posts

Posted - 2010-08-31 : 08:24:43

ok....

I write this

alter trigger AnyName on Cycles
for insert,update,delete
AS
BACKUP DATABASE medrepcrm TO DISK = N'C:\medrepcrm.bak' WITH NOFORMAT, INIT, NAME = N'pcrm-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

that lead to this error after insert,update or delete on this table

Msg 3021, Level 16, State 0, Procedure AnyName, Line 8
Cannot perform a backup or restore operation within a transaction.
Msg 3013, Level 16, State 1, Procedure AnyName, Line 8
BACKUP DATABASE is terminating abnormally.
The statement has been terminated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-31 : 08:49:52

It is a bad idea to backup a complete database every time when 1 record in 1 table is modified / inserted / deleted.
Why do you want to do that?
Maybe we can point you into another direction if we know what's the reason to do something like that...

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

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-31 : 09:04:25

quote:
Originally posted by M.Hazem.1987

Yes....
so I want to take a full database backup when this table is modified.
but my code lead to error

So if there are 100 modifications made to the table each hour, you want 100 backups an hour?
Really?

quote:

quote:

There's likely a better way.

what do you mean ?like what ?

Don't know yet, you haven't explained the reasoning for this

--
Gail Shaw
SQL Server MVP

M.Hazem.1987
Starting Member

5 Posts

Posted - 2010-08-31 : 14:48:48

ok...
This table is modified (once time every 1 year).....(in my system the admin add new row every 1 year....and may be he update it every 6 month)

As you see this table (Cycle) and there is one cycle in my system in year.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-31 : 16:35:52

And what, you want to take a special backup, one outside of the normal backup chain, when that table gets updated?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources