Table Creation And Attaching Trigger Using CLR
Apr 4, 2008
I want guidance for the following problem
I have to create one table and attach the trigger to the server given by the user. Assuming I have all sorts of permissions, how can I do this using CLR.
I tried this using data base project in c# and deploying it manually, it worked successfully but I am not able to get how can I provide this at run time to the user.
If I write one assembly on the client side for the same than how to run it on the back end (i.e., SQL Server).
View 1 Replies
ADVERTISEMENT
Feb 6, 2008
I have created a trigger on a table for delete operation. I want to fire the trigger against each row deletion. When I applay a query that delete 5 rows, the trigger should execute five times, that is, some work to do for each row.
How can I handle this? Is there any keyword to use when create the trigger?
View 3 Replies
View Related
Apr 8, 2008
Very new to SQL Server - using Microsoft SQL Server Management Studio, I'm trying to create a Trigger but I get an error saying that the table doesn't exist or is invalid -- "Object 'dbo.tblPropertyValue' does not exist or is invalid for this operation". The table does exist. Following is the trigger (I replaced the actual code w/ a simple PRINT statement):
CREATE TRIGGER [dbo].[tr_PropValChange] ON [dbo].[tblPropertyValue] FOR UPDATE AS
BEGIN
PRINT 'Hello'
END
GO
I'm able to create a stored procedure that accesses that table. The database (MyDb) is running on my laptop (computer name=MyComputer) and owned by my windows account (MyComputerBill). All db tables are prefixed with 'dbo.'. I connect to Management Studio using the sa id. We're using SQL Server 2005 (Product Version: 9.00.1406.00, Product Level: RTM, Edition: Developer Edition).
Thanks,
Bill
View 4 Replies
View Related
Feb 2, 2006
Hello,
Is there a way to trigger the execution of a procedure when a database is created ? We're using this 3rd party system in which you only specify the database server. Whenever the users create a new "Storage Area", the system actually creates a new database.
I need to handle backups for that system so I want to create a procedure that will generate a backup device and backup jobs automatically whenever a database is created.
I will also need 2 procedures to handle database renaming and deletion. I searched around but can't find a way to trigger the execution of my procedures.
View 4 Replies
View Related
Feb 28, 2014
I need a trigger to set the creation date of a new record in the database... I tried the following, but it changed all records, not just the new one...
CREATE TRIGGER trgCreationDate
ON [dbo].tabCustomerLookup
FOR INSERT
AS
BEGIN
UPDATE tabCustomerLookup
SET CreationDate = getdate()
END
View 3 Replies
View Related
Jan 6, 2014
We have created a DDL trigger on SQL server 2005 database for DB audit purpose. Following is the script used for trigger creation
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChangeLog](
[Code] ....
After the DDL trigger creation. Application team started reporting following error while executing a stored procedure.
*********************************
Error 1:
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
Error2:
[Execute SQL Task] Error: Executing the query "exec sp_drop_indexes_EnhLeaseData delete from dbo.leases where vin_num='XXX' and lease_acct_num='XXXX' delete from dbo.leases where vin_num='XXX' and lease_acct_num='080066225' " failed with the following error: "INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 1 Replies
View Related
Jan 23, 2008
Hi,
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
where i.req_guid = d.req_guid
and i.req_fname <> d.req_fname
Thanks,
leo
View 7 Replies
View Related
May 20, 2008
I have multiple Common Table Expressions i.e CTE1,CTE2,...CTE5.
I want to Create Temperory Table and inserting data from CTE5. Is this possible to create?
like:
create table #temp (row_no int identity (1,1),time datetime, action varchar(5))
insert into #temp select * from cte5
View 4 Replies
View Related
Dec 20, 2005
Hi,
First time poster here, basically I have a second year university module on database design and for our coursework we have to model and create a database. One of the questions asks us to create a table that has a constraint on how many rows it can contain. I now that this is possible in some other databases, however I haven't seen a constraint that I could use on create table to limit the number of rows.. Does anyone now if this is possible?
Thanks,
Al
View 5 Replies
View Related
Apr 19, 2007
Hi,I'm trying to create some tables dynamically based on the content of another table in the same database. I found a post that does what I want to do, but I can't get my code (that is similar to the post) to work.Given below is my code: 1 DECLARE @deptCode varchar(50), @numberOfDept int, @tableName varchar(MAX), @columnName varchar(MAX)
2 DECLARE @lengthDeptCode int, @lengthTableName int, @lengthColumnName int
3
4 SELECT @numberOfDept = COUNT(DISTINCT DeptCode)
5 FROM tbl_Department;
6
7 WHILE (@numberOfDept >=0)
8 BEGIN
9 SELECT @deptCode = DeptCode, @lengthDeptCode = LEN(DeptCode)
10 FROM tbl_Department;
11
12 SET @tableName = 'tbl_ProjectNumber'+@deptCode
13 SET @lengthTableName = LEN(@tableName)
14 SET @columnName = 'ProjectNumber'+@deptCode
15 SET @lengthColumnName = LEN(@columnName)
16
17 CREATE TABLE CAST(@tableName as char(@lengthTableName))
18 (
19 CAST(@columnName as char(@lengthColumnName)) int IDENTITY(1,1) NOT NULL
20 )
21
22 SET @numberOfDept = @numberOfDept - 1
23 END
This is actually my first time using SQL programatically so I'm guessing there are alot of problems with it. I just don't know what exactly. The error I get is:Msg 102, Level 15, State 1, Line 18Incorrect syntax near '@tableName'. Thanks.
View 1 Replies
View Related
Jun 19, 2007
Hi there,I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called@TABLENAME VARCHAR(16)and when i say SELECT * FROM @TABLENAMEit is giving me an error and I think I cannot declare @TABLENAME as a table variable because I do not want to create a temp table of sorts.I hope I am clear.thanks,Murthy here
View 6 Replies
View Related
Jan 24, 2008
Hi All,
DECLARE @MyTableVar table( EmpID nvarchar(10) )
select login_id from cpm into @MyTableVar
Above syntax is not working....
Actually i want to table variable and store the result returned by stored procedure.
How to do that...
Thanks and reagards
A
View 3 Replies
View Related
Mar 20, 2008
I'm working on building my database tables, and I've ran into a bit of a situation.I have one table called 'Entities' that will hold information about people, places, and events. I would like to have the user be able to add other properties to these entities ad hoc.For example, a person might have a birthdate they want to record. So I was going to setup a DateProperties table that would have be setup something like this:DatePropertyIDEntityIDName (this would be 'Birthdate' in this situation)Value (the DateTime value)In that same way, I would also want users to be able to add something like Population to the places. So I was going to setup a NumberProperties table that would have something like this:DatePropertyIDEntityIDNameValueI noticed the structures for the table is basically the same; the only thing that is changing is the Data Type for the Value. I was thinking there has to be an easier/better way to do this sort of thing, perhaps all in a single table. I'm still trying to learn all the latest stuff from .NET 3.5. I was hoping to use LINQ to SQL, but I am willing to try anything.Any suggestions?
View 1 Replies
View Related
Oct 24, 2005
Hello friendsI want to have a trigger for creating table on dialy basis.for e.g. I have a table say Data and on each day a new table is created like Data24Oct05 and so on. please help for writing the trigger for the same.thanks
View 4 Replies
View Related
Feb 14, 2002
Hi!
After table creation I have got a message:
Warning: The table 'test' has been created but its maximum row size (11864) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
What does it mean?
Thank you,
Eve
View 1 Replies
View Related
Dec 14, 2006
Hi everyone and Happy Holidays!
I've got a problem with table creation in stored procedures (SQL Server 2000). We've got an application where the user login only has rights to execute stored procedures. The problem is that a stored proc is dynamically creating a table and so the owner of that table is being assigned to whatever login the application is using instead of dbo. It's causing numerous issues. Is there any way that this can be avoided or changed without granting the user sa privileges?
Thanks in advance,
Cat
View 14 Replies
View Related
Mar 12, 2012
I am starting to create a new database table based on an existing dbtable. My existing table has a list of ID's and a date range for each ID.
For example:
TableSource
ID Start DateTime End datetime
Y10012 01-12-12 13:00:00 01-19-12 13:00:00
So for this ID, I need my SQL statement to read this table, then create a new table and insert a new row for every second starting from the start date to the end date. I have several id's that span a week at a time. So I am expecting millions and millions of records once I am done.
End Result:
MainTable
IDFullTime
Y1001201-12-12 13:00:00
Y1001201-12-12 13:00:01
Y1001201-12-12 13:00:02
Y1001201-12-12 13:00:03
....
Y10012 01-12-19 13:00:00
Then once it completes reading S1001, it moves on to the next ID and appends it to the table. The date ranges are different for each ID- so it can't be hard coded.
View 5 Replies
View Related
Feb 17, 2004
Hi,
Can you create a table on one line and then update it on the next i.e. in the same query.
i.e.
create table test as select * from original
update test set test.a = .......
I know that they both work individually but whne i try to run them in one file i get "sql command not properly ended" - when i put a comma after 'original' i then get "invalid table name".
Am i just missing something
Cheers
View 4 Replies
View Related
Mar 19, 2004
Suppose I have a table named table1 which has a field question_Id.There are many values for this field say 100,101,102.
Now I want to make a table with the field name 100,101,102 but the problem is that it is not fixed how many values are there for question_id.Here in this example I mentioned three(100,101,102) but it may be anything.How can I make a table with the field names this way?
Subhasish
View 3 Replies
View Related
Apr 2, 2008
CREATE DATABASE Dummy
ON
-- Primary file contains Startup information of the database
PRIMARY (
NAME = PrimaryLog,
FILENAME = 'D:primary.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
-- Holds The Data of LookUPTables,TPProfile,CRM.
(
NAME = Data,
FILENAME = 'D:Data.ndf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
LOG ON
-- Stores The Log Information used To Recover The Database
(
NAME = Log,
FILENAME = 'D:Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB )
Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
View 1 Replies
View Related
Mar 2, 2007
Hi,
i'v send this email to understand how i can replicate a creation's table between two sql servers ?
the documentation of the replication explain the replication use the alter table !!
it's possible to detect with a query this creation of table and start after the replication ?
another solution ?
Regard's
View 1 Replies
View Related
Apr 2, 2008
CREATE DATABASE Dummy
ON
-- Primary file contains Startup information of the database
PRIMARY (
NAME = PrimaryLog,
FILENAME = 'D:primary.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
-- Holds The Data of LookUPTables,TPProfile,CRM.
(
NAME = Data,
FILENAME = 'D:Data.ndf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
LOG ON
-- Stores The Log Information used To Recover The Database
(
NAME = Log,
FILENAME = 'D:Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB )
Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
View 1 Replies
View Related
Nov 14, 2006
Hey,
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Thanks.
View 4 Replies
View Related
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Dec 5, 2006
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}
The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.
Comments and Suggestion will be much appreciated.
View 16 Replies
View Related
Oct 1, 2006
I'm wondering if there is a control available for creating/modifying db tables through a web interface. I want for users to be able to add/remove, rename, and change the datatype of certain fields in a database table. I've been searching all day online if such a control exists in asp.net but haven't found anything.
View 1 Replies
View Related
Sep 29, 2014
I have a table I am trying to create and one of the columns needs to have a decimal that appears as 00.00 and does not round because the data example is 87.09, 86.50, 98.55 etc. I have tried every type of decimal formatting when creating I can find and nothing has worked. When I create the table and upload the data it keeps rounding like this: 87.00, 87.00, 99.00
The only way it does not round is if I do it as a char but that does not seem right when dealing with numerics.
View 1 Replies
View Related
Apr 8, 2006
hello alli want to create a phone table and it contains two fields empid ,ph.the phone table following format:Phone table------------------------------------------empid ph----- ---------------------------------office Mobile home--------- -------- --------100 9380768532 98455555 98822213--------------------------------------------i want above type of format and then how to insert into values thatphone table . please help me.
View 2 Replies
View Related
Jul 25, 2007
Hi,
when I select sql server provider pointing to connection mydatabase for Logging the sysdtslog90 table is not getting created. However if I point the connection to msdb - sysdtslog90 it works fine.
Can I have sysdtslog90 under the application database? what are the steps to do this?
Thanks in advance.
Prabhakaran
View 4 Replies
View Related
Oct 30, 2007
Table 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
View 17 Replies
View Related
Feb 5, 2008
A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger
on a
for insert
as
insert into b(ID)
select i.id
from inserted i
where
--specific USER
View 9 Replies
View Related
Dec 29, 2004
I am using the below SQL to insert a table. The problem is after I run this, I run another script to populate the table (see below). The population script will work if I run it as INSERT INTO ... SELECT TOP 99.9999999 PERCENT ..., but if I put 100 PERCENT, or just use no percent limiter I get the following error: Msg 8624, Internal SQL Server error.
It is weird b/c once something is inserted in the table, i can run the populate script without any problems. Any idea as to why this is happening?
Thanks,
Dave
TABLE GENERATION SCRIPT
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbCelebroAds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbCelebroAds]
GO
CREATE TABLE [dbo].[tbCelebroAds] (
[AdID] [int] IDENTITY (1, 1) NOT NULL ,
[BranchCode] [int] NOT NULL ,
[PropertyID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AdScheduleCode] [int] NOT NULL ,
[RecAtCentral] [int] NULL ,
[AdRan] [int] NOT NULL ,
[AdStatusID] [int] NOT NULL ,
[PatID] [int] NULL ,
[RunDate] [datetime] NOT NULL ,
[CreationCost] [float] NOT NULL ,
[BillCost] [float] NOT NULL ,
[AddedDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
TABLE POPULATION SCRIPT
INSERT INTO tbCelebroAds (BranchCode, PropertyID, AdScheduleCode, PatID, AdStatusID, AdRan, RunDate, CreationCost, BillCost, AddedDate)
SELECT [TOP 100 PERCENT or no percent limiter doesn't work, TOP 99.9999999 PERCENT does] BranchCode, PropertyID, AdScheduleCode, cp.PatID, 6, 0, CAST(PubDate AS DATETIME), CAST(ISNULL(pat.Cost,0) AS DECIMAL(10,2)), 0, GetDate()
FROM tbCelebroView cv
LEFT JOIN tbCelebroPubs cp ON cv.PublicationName = cp.PublicationName AND cv.AdSectionName = cp.AdSectionName
LEFT JOIN tbPubToAdType pat ON cp.PatID = pat.PatID
WHERE CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) NOT IN
(SELECT CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) FROM tbCelebroAds)
View 4 Replies
View Related
Sep 11, 2015
I've done already the schemas for all the tables a you suggested.But when I created a new table the dbo prefix (dbo.ADM.NewTable) appears.I'd like this dbo not to automatically appear whenever i created a new table — as it requires me to alter whenever I created a new table.Is this something that is required to determine the creator of the table?Otherwise, I'd like that to remove.
View 7 Replies
View Related
