Creating trigger with update

Hello

I am new to sql and asp, I am using visual web developer and have table that when it gets change I would like to see another table populated with the information. This is the two tables I have

First one has the information in it that users will insert in it

asset_id int Unchecked
asset_desc varchar(50) Checked
serial_no varchar(50) Unchecked
model_no varchar(50) Checked
category bigint Unchecked
Manufacturer varchar(50) Checked
Mac_address varchar(50) Checked
service_pack varchar(50) Checked
owner bigint Unchecked
location bigint Unchecked
date_acquired datetime Checked
date_deactivated datetime Checked
system_asset_no varchar(50) Checked
cs_desc varchar(50) Checked
vendor varchar(50) Checked
modified_date datetime Checked
action varchar(50) Checked
Unchecked

Next table is the one I want the information to go in

history_asset_id int Unchecked
history_asset_desc varchar(50) Checked
history_serial_no varchar(50) Checked
history_model_no varchar(50) Checked
history_category bigint Checked
history_manufacturer varchar(50) Checked
history_mac_address varchar(50) Checked
history_service_pack varchar(50) Checked
history_owner bigint Checked
history_location bigint Checked
history_date_acquired datetime Checked
history_date_deactivated datetime Checked
history_system_asset_no varchar(50) Checked
history_cs_desc varchar(50) Checked
history_vendor varchar(50) Checked
[modified date] datetime Checked
action varchar(50) Checked
Unchecked

the column action is for the name of person updating and modified date is the system date. My trigger is this

Create TRIGGER Trigger4
ON dbo.t_asset
FOR INSERT /* INSERT, UPDATE, DELETE */
AS
INSERT INTO history_asset_id
(history_asset_id, history_asset_desc, history_asset_orderno, history_asset_invoiceno, history_asset_yellowno, history_asset_serial_number,history_asset_cost, history_asset_fedpart, history_date_acquired, history_asset_cond, history_cat_id, history_bld_id, history_loc_name,history_date_deactivated, history_asset_dispvalue, action, modified_date)
VALUES ('@.asset_id','@.asset_desc','@.asset_orderno','@.asset_invoiceno','@.asset_yellowno','@.asset_serial_number','@.asset_cost','@.asset_fedpart','@.date_acquired','@.asset_cond','@.cat_id','@.bld_id','@.loc_name','@.date_deactivated','@.asset_dispvalue','@.action','@.sysdate')

Can anyone please help me or point me in the right direction, rbynum@.kansascommerce.com

Hi,

perhaps you first shoud read some concepts about triggers before implementing them.

Don′t wanna be a whippersnapper :-), this is a serious suggestion, most people (or even many) implemented triggers without having a basic understanding of them, like this, that triggers are fired on statement level rather than on a row level. So this can turn out to a real problem as data can be left in an inconsistent state if you don't care about that issue.

YOu will need the information and the understanding that information about the deleted / updated / inserted data is stored in virtual tables which are accessible at the following stages:

INSERT operation: INSERTED
UPDATE operation: INSERTED, DELETED
DELETE operation: DELETED

So in your case that would be something like:

INSERT INTO history_asset_id
(history_asset_id, history_asset_desc, history_asset_orderno, history_asset_invoiceno, history_asset_yellowno, history_asset_serial_number,history_asset_cost, history_asset_fedpart, history_date_acquired, history_asset_cond, history_cat_id, history_bld_id, history_loc_name,history_date_deactivated, history_asset_dispvalue, action, modified_date)
Select <columnlisthere>
FOM INSERTED

If you have any further question, don't hesitate to come back.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

You need to reference the inserted/updated/deleted rows inside the trigger code using the inserted/deleted virtual tables. See the Books Online topic on CREATE TRIGGER for more details. For the insert trigger above, you need to reference the inserted table like:

Create TRIGGER Trigger4
ON dbo.t_asset
FOR INSERT /* INSERT, UPDATE, DELETE */
AS
INSERT INTO history_asset_id
(history_asset_id, history_asset_desc, history_asset_orderno, history_asset_invoiceno, history_asset_yellowno, history_asset_serial_number,history_asset_cost, history_asset_fedpart, history_date_acquired, history_asset_cond, history_cat_id, history_bld_id, history_loc_name,history_date_deactivated, history_asset_dispvalue, action, modified_date)
select asset_id, asset_desc....

from inserted

sql

creating the database tables

Hi,

I need to create a table and a few fields in the SQL database programmatically in asp.net.

I know the tabes name, the server which it resides on, username/password. Would anyone have a small sample of this code?

Thanks

radI meant to say, "I know the database name."|||You might check out this article to get you started:Creating a SQL Server Database Programmatically.|||Thanks|||This is the code that I came up with. When I load the page it tells me that my error is here "Line 22: cmdCreate.ExecuteNonQuery()". What am I doing wrong?

<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %
<Script Runat="Server"
Sub Button_Click( s As Object, e As EventArgs )

Dim conUsers As SqlConnection
Dim strCreate As String
Dim cmdCreate As SqlCommand

conUsers = New SqlConnection( "Server=servername;UID=user;PWD=password;database=databasename" )

strCreate = "CREATE TABLE myTable" + "(Application CHAR(100), Project CHAR(100), EnteredDate CHAR(100), EnteredBy CHAR(100), Descriptions CHAR(100), SNPT CHAR(100), Automation CHAR(100), ContactName CHAR(100), ContactNumber CHAR(100), Priority CHAR(100), Status CHAR(100))"

cmdCreate = New SqlCommand( strCreate, conUsers )
conUsers.Open()
cmdCreate.ExecuteNonQuery()
conUsers.Close()

End Sub

</Script>|||Exactly what is the error message? That will help a great deal.|||I could guestimate that it's probably because you aren't setting the cmdCreate.CommandType, your UID for your server isn't a dbo in that database, you should have a space after myTable (just personal thing, shouldn't generate error), table of that name already exists..

yeah, we need what the error is before we can help you.|||Kragie, I have no idea what most of these things you said mean. I am new at asp.net.

The only error it gives me is, "CREATE TABLE permission denied in database 'databasename'.|||OK, the problem is that the SQL Server user does not have permission to add a table in the database. Give the user permission using Enterprise Manager, or use SQL Script in Query Analyzer.

USE databasename
go

GRANT CREATE TABLE TO username
go|||Thanks for the help guys, although I think the error might be incorrect.

Creating tables from ASP.

Hi list, i wanto to use an ASP page to create tables from the devolopers
intranet.
Inside Code, i have the following conn string:
set objRS = CreateObject("ADODB.Recordset")
objcon.connectionstring = "Provider=SQLOLEDB.1;User
ID=sa;Password=XXXXXX;Persist Security Info=True;Initial Catalog=master;Data
Source=" & server
I have one string called sqltxt that uses the Create table statement. this
is the execution code: "3704,Operation is not allowed when the object is
closed"
Whats wrong'
Thanks.
objRS.open sqltxt, objcon
response.write err.number & "," & err.description & "<p>"
response.write sqltxt
ObjRS.CLOSE
When i try to execute this, i recieve the message :As a general suggestion, consider using a ADO command object instead of a
recordset object.
Anith|||Can u tell me how?
"Anith Sen" wrote:

> As a general suggestion, consider using a ADO command object instead of a
> recordset object.
> --
> Anith
>
>|||Assuming you really wanted to do this, you should use the Execute method off
the
Connection object like so:
(ah VBScript...Forgot how joyous it was <insert sarcasm> ;-> )
Dim oConn
Dim sSQL
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = ...
oConn.Open
sSQL = "Create Table...
oConn.Execute sSQL
Set oConn = Nothing
HTH
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:1CBE6886-30B6-4FD2-995E-DFFEC08D7123@.microsoft.com...
> Hi list, i wanto to use an ASP page to create tables from the devolopers
> intranet.
> Inside Code, i have the following conn string:
> set objRS = CreateObject("ADODB.Recordset")
> objcon.connectionstring = "Provider=SQLOLEDB.1;User
> ID=sa;Password=XXXXXX;Persist Security Info=True;Initial Catalog=master;Da
ta
> Source=" & server
> I have one string called sqltxt that uses the Create table statement. this
> is the execution code: "3704,Operation is not allowed when the object is
> closed"
> Whats wrong'
> Thanks.
> objRS.open sqltxt, objcon
> response.write err.number & "," & err.description & "<p>"
> response.write sqltxt
> ObjRS.CLOSE
> When i try to execute this, i recieve the message :|||Thanks for help Thomas, it works fine!!!
Now, i have a doubt.
When the transaction occurs, i use the err.number and err.description to see
any errors, but i coul not view anything. what do i need to see the "The
command(s) completed successfully."
Thanks again
"Thomas Coleman" wrote:

> Assuming you really wanted to do this, you should use the Execute method o
ff the
> Connection object like so:
> (ah VBScript...Forgot how joyous it was <insert sarcasm> ;-> )
> Dim oConn
> Dim sSQL
> Set oConn = CreateObject("ADODB.Connection")
> oConn.ConnectionString = ...
> oConn.Open
> sSQL = "Create Table...
> oConn.Execute sSQL
> Set oConn = Nothing
>
> HTH
>
> Thomas
>
>
> "Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
> news:1CBE6886-30B6-4FD2-995E-DFFEC08D7123@.microsoft.com...
>
>|||> When the transaction occurs, i use the err.number and err.description to seed">
> any errors, but i coul not view anything. what do i need to see the "The
> command(s) completed successfully."
From ASP? Of course not. From ADO code, you know that a command executed
successfully if an error was not thrown. If we are talking about VBScript,
you'll have to check for Err.Number right after you execute the statement li
ke
so:
sSQL = "Create Table...
oConn.Execute sSQL
If Err.Number <> 0 Then
..call error handler or do whatever in response to an error...
End If
Set oConn = Nothing
Since we're talking about the creation of the table, if the create table cal
l to
ADO fired successfully, then you should be ok to query against it or put dat
a in
it.
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:A2338E8C-02A9-48DB-936C-325B78F9E811@.microsoft.com...
> Thanks for help Thomas, it works fine!!!
> Now, i have a doubt.
> When the transaction occurs, i use the err.number and err.description to s
ee
> any errors, but i coul not view anything. what do i need to see the "The
> command(s) completed successfully."
> Thanks again
> "Thomas Coleman" wrote:
>
<snip>|||Tinchos,

> Hi list, i wanto to use an ASP page to create tables from the devolopers
> intranet.
> ...
> Whats wrong'
Although Thomas already answered your question, you should really rethink
your approach. Very little good can come from allowing tables to be created
by the application ... why are you doing this in the first place?
-- Alex Papadimoulis|||I want to offer a service to developers (only datareader and datawriters) th
e
possibility to create custom tables or execute some scripts. I think its the
best solution to have less workload for me.
"Alex Papadimoulis" wrote:

> Tinchos,
>
> Although Thomas already answered your question, you should really rethink
> your approach. Very little good can come from allowing tables to be create
d
> by the application ... why are you doing this in the first place?
> -- Alex Papadimoulis|||So, how can i parse errors like "Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'table' in the database" '
Objcon.execute sqltxt
if err.number = 0 then
Response.write "Ok...
else
response.write err.number & "," & err.description & "<p>"
end if
if i try to create the table twice i dont recieve any error messages...
Thanks again!!!!|||Firstly, the system will throw an error if you execute a Create Table statem
ent
and the table exists. You need to be mindful of the *owner* of the table.
Specifically, make sure you prefix the table name with the owner like so:
Create Table dbo.TableName
(
..
)
Otherwise, the table will be created under the user's database name. So you
might get Joe.TableName, Fred.TableName and even dbo.TableName.
Secondly, you need to look at the Errors collection on the Connection object
and
trap the specific error number associated with the a duplicate table name.
Thus..
oConn.Execute sSQL
Select Case Err.Number
Case 0
'do nothing
Case 12344 <-- place the actual error number here
..tell user there was a duplicate
Case Else
..display general error
End Select
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:AB4A1EB6-1374-43DD-9722-48BEA85AF2D5@.microsoft.com...
> So, how can i parse errors like "Server: Msg 2714, Level 16, State 6, Line
1
> There is already an object named 'table' in the database" '
> Objcon.execute sqltxt
> if err.number = 0 then
> Response.write "Ok...
> else
> response.write err.number & "," & err.description & "<p>"
> end if
> if i try to create the table twice i dont recieve any error messages...
> Thanks again!!!!

creating new users with dbo permissions

I am trying to create a new user for a SQL Server database and use the credentials in an
ASP.NET app.

Problem is dbo permissions are not being applied to the database objects when I set up a
new user (Logins -> New Logins) with SQL Server Authentication, set the default database to
the database I want.

I then get an error message saying that the user has not been granted access to their default
database : DBNAME and therefore will not be able to gain access to their default database.

I'm not sure why this is as I am logged in as sa.

Anyway, when I ignore the error and set-up this new user through the Users section of the
database none of the dbo permissions carry over.

Can anyone help?
Cheers.SQL Server's default authentication is via Windows only and you have to invoke
authentication via SQL Server usernames/passwords by right clicking on the server name, selecting the Security tab and making the change.

creating database

I am working on site in asp.net with c# and using database sql server 2000 and managing all the data on server i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table.

tell me how many tables should i maintain in one database. and what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload

please guide me i dont have an idea.

thanks for spending ur valuable time for me.

I suppose that would depend on the database structure of your application. If your application required more tables i guess you create one in the existing database it self.

I dont think you need to worry much with only 15 tables in the database. Frankly your db can handle many tables still.... :) so chill

I would suggest create a unique db for each application. Again if there are very few tables in each application u can use the same database as well. There are many tweaks in the database than just the no. of tables to make it faster... so i will not consider more tables as a major problem for bad database perfomance.

|||

Sharvari:

I am working on site in asp.net with c# and using database sql server 2000 and managing all the data on server i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table.

You would generally create a new database for each individual application unless several applications may share the data.

Sharvari:

tell me how many tables should i maintain in one database. and what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload

There is no set limit that defines how many tables you should have. The important thing when it comes to database efficiency is the table design, adequate levels of normalisation, proper indexing, and write good optimised SQL for data retrieval.

|||

hi,

read this link this may be helpful to you.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0001029.htm

|||

Sharvari:

i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table.

You can go on creatng as many tables/stored procedure/functions/views as you want in a database. There is indeed alimit of maximum number of objects that can be created in a database but don't worry you are far...far away from that.

Sharvari:

what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload

There are many techniques for better administration of SQL Server. Just try a google and you'll find so many informative and useful articles.

|||whats the effect on server by using table and whats the effect on server by using store procedure which is more convinient. thanks for spending ur valuable time for me.|||

Do you mean that you will use stored procedure to get the data or use a table to get the data?

|||

Well, I can't exactly understand what you are trying to ask. Anyways, I think you are trying to compare tables and stored procedures. But, tables and stored procedures have different objectives.

Tables are used to store the data in a database.

Stored Procedures are used to modify the data stored in the tables. By modify I mean Insert/Update/Delete the data.

So, basically both of these 2 have different purposes and there should be no comparison between these 2.

|||

Sharvari:

whats the effect on server by using table and whats the effect on server by using store procedure which is more convinient. thanks for spending ur valuable time for me.

As mentioned, tables are where you store the data. Stored procedures provide a mechanism which allows you to encapsulate logic into a unit which can be re-used. Therefore a stored procedure can contain logic on how to select/update/delete data and various other things.

Creating Database

How do you create a SQL database (Sql Express 2005) in real time using VB.Net 2005 ASP.net 2.0?Answer Found! For those who want can visithttp://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994 to view sample code|||

A good sample can be downloaded here:

http://download.microsoft.com/download/C/3/8/C3888A3E-52F8-4FE9-8E41-89150AB0302F/CreateDB.zip.exe