for Jack (jfrost10) 1

[Alcar]

I read in a thread that you are studing and getting ready to take the SQL Server Certification.
Here is a little question just to keep you ready hehehe
(what are friends for after all?)

How would I write a Trigger that would check if an account exists or not when a new Client is created or updated.
Of course, if it doesn't exisit, the trigger should create the account.

Tables: tblClients, tblAccounts
Relation: tblClient.pkClientID = tblAccount.fkClientID

go get them buddy!!

ps. yes, I want the answer hehehe

 

[jfrost10]

Oh, there better be a star in this for me!


K, I need a bit more info though:
-Whats the cardinality between clients and accounts
i.e. can 1 client have only 1 account, or multiple
accounts, or even 0 accounts?
-What is the rule between them
i.e. If a client is created, an account is created
automatically?

And hey, any other SQL questions you have, throw them my way! I like exercises like this.


Jack

 

[Alcar]

Relation is 1 to 1, 1 client = 1 account.
client created, account created. client deleted, account deleted.
After this one I'll have another one for you... more on the management part of SQL Server =)
If this thread disturbs others in this forum, because of the inapproriate topic, please let us know.
I just think that some DataBase knowledge is always useful, no matter what you are developing =)

 

[jfrost10]

K, well this is an easy one then. Here's what you do:
in the enterprise manager, right click the client table and select AllTasks>Manage Triggers
Then add this

CREATE TRIGGER trig_CreateAccount ON [dbo].[tt_client]
FOR INSERT
AS
Insert Into tt_account
(ClientID)
Values
(@@identity)

Here's whats happening:
- I have two tables (Client, Account). Client has a primary key of ClientID. The identity is set to Yes, starting at 1. The Account table is set the same way.
- When you insert a record into the Client table, the way I have the unique identifiers set up to be generated by sql server, the value is held in the @@identity variable, which is a sql server generated variable
- This trigger will fire whenever an INSERT is done (so updates will not trigger it). It performs an insert into the Account table by adding ONLY the ClientID value. This is because the identity set in the Account table will create the record's unique identifier automatically.

As for the delete part, the easiest way is to set up a constraint that will cause a cascade delete: if client is deleted, anything that depended on that client record gets toasted as well. Unfortunately (heh), I havn't gotten that far in my book yet.


Bring on the next one! w00t!


jack

 

[Alcar]

Neat!! (interesting notation you have)

Here is the next:

A new Developer workstation has been added to a domain, and the policy of the company is to have a local SQL Server DB copy of the current project. Therefore there is the need to create a backup of the DB and move it to the developer station.
What would be your best option considering that the DB is always running with many active connections. The DB has n tables and relations and last but not least, the DB is located out of the location. (18 miles if you really wanna know LOL)

To summarize:
- compress-backup original DB
- different exports options

=)

 

[jfrost10]

k, here's the quick and dirty way:
On the real db (not the new development one), right click on the desired db, select AllTasks>BackupDatabase.

Add a location if one isn't supplied, and click ok. This will create a backup file at the specified location. You can then send the file to the developer (either store it on disk and courier it over, or encrypt it and email it over), where they can perform a Restore Database on their machine.

This works well if the database is small, and there isn't alot of data. However, if its a really big db and you don't necessarily need the data transferred over, this isn't an ideal option.

Actually, thats a good question: does the developer NEED the existing data, or just the database schema?

I'll post a few other options as I uncover them.


Jack

 

[jfrost10]

Here's another option Al, but only if you DON'T need the data on teh devloper machine, just the db structure:

Highlight the tables/storedprocs/functons you want to carry over. Then right click, select AllTasks>GenerateSQLScript

On the Options tab, select "Create One File" Then click ok.

This will create a text file that contains a whole wackload of script to create all the highlighted objects. It won't keep the data in the table, but it will keep the actual structure (pk, contrainsts, etc.). You'd just have to send the file to the developer (since its text, it could be emailed no problem, although encrypting it first would be a good idea).

Now the only thing is that it can't generate system tables or other sytem objects, so the developer will need to have created a database instance of the same name, which the script could be run against.

jack

 

[Alcar]

Interesting. I had both options in mind and almost opted for the script one but I would have not figured out that I needed a pre-existing table structure on the dev machine.
It's good to know this.

* here is an extra star hehehe

 

[jfrost10]

Oh! Sorry, sorry, I didn't explain that well then:

You don't need the TABLE STRUCTURE on the dev machine, you just need a DATABASE created to run the script against.

The script won't create the database, only the database objects like tables, stored procs, etc.

Sorry bout' that


Jack

 

[Alcar]

Bingo =)
well if you think about it, even with the restore database you need a pre-exisitng empty database object where you will then restore the file.

 

[jfrost10]

Yeah, thats true.

We're going to be looking into how to update a SQL server from another one accross different networks, and if we figure it out I'll let you know. That would be the ideal way to do it, server to server; no files, no backups, no need to create emtpy db's first, etc.

It's just a matter of getting it to work though


Jack

 

[Alcar]

Off the bat you will need a VPN and probably a nice little .net application that opens the first db and replicates it to the next one...
...mumble mumble...
I like that idea...

 

[jfrost10]

I hope we don't need a VPN, there's no way we could get that set up. I hear ya though, I'm not sure how to safely transfer the data between the two unless there's some application taht encrypts it at both ends (not sure if SQL Server includes that on its own).

Jack