Trigger In SQLserver

May 6, 2008

Hi friends,

We have triggers on each table in oracle database and none in sqlserver so far. But we are looking at creating triggers on sqlserver db's as well and I am trying to find the equivalent syntax for T sql..

In ORACLE, for edi_invoice column on EDI table:

CREATE OR REPLACE TRIGGER edi_trg
before insert or update ON edi for each row
begin

:new.EDI_INVOICE:= nvl(:new.EDI_INVOICE,' ');
end
/

I think in T SQL is should be like below?

CREATE TRIGGER edi_trg
ON edi
for insert or update
as

:new.EDI_INVOICE:= nvl(:new.EDI_INVOICE,' ');
go

Thanks much for all your help.

View 11 Replies


ADVERTISEMENT

Problem With Trigger In Sqlserver

Jun 30, 2006

Hi,I have a trigger in mysql as below...I need to change it into sqlserver.I am unable to find replacement to 'for each row'.Can u please let me know how can we change this trigger into sqlserver format.
CREATE TRIGGER TGR_ENTITY_TYPE_ON_ADD
BEFORE INSERT
ON ENTITY_TYPE
FOR EACH ROW
SET NEW.DATE_ADDED=NOW(),
NEW.DATE_MODIFIED=NOW();
Thanks in advance,
MLR

View 2 Replies View Related

[ask] Trigger In Sqlserver Not Working, HELPP!!

May 9, 2007

i have this trigger in my database : ALTER TRIGGER dbo.AddVoucher    ON  dbo.User_AddVoucher    AFTER INSERTAS     SET NOCOUNT ON;DECLARE @UserId int,     @Add_id int,     @voucher_id char,     @Kredit money,     @date smalldatetime,     @last_balance money,     @voucher_status charSELECT @UserId = UserId,     @voucher_id = Voucher_ID,     @Add_id = Add_id,     @date = Deposit_Date    FROM InsertedSELECT @Kredit= Voucher_Value,     @voucher_status = Voucher_Status     FROM Voucher     WHERE Voucher_ID = @voucher_idINSERT INTO User_Balance(AddVoucher_ID, UserId, Update_Type, Update_Date)    VALUES     (@Add_id,@UserId, 'Kredit',@date)select @last_balance = Balance    from User_Balance     WHERE UserId = @UserId and Balance = (select TOP 1 Balance User_Balance where UserId = @UserId order by Update_Id DESC) if (@voucher_status = 'active')    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    -- Insert statements for trigger hereBEGIN    update User_Balance    set Balance = @last_balance + @Kredit    where AddVoucher_ID = @Add_id    update Voucher    set Sold_Date = @date    where Voucher_ID = @voucher_idENDELSE BEGIN    raiserror ('Voucher is not valid',0,1)    rollback transaction ENDgo  the problem is the update function is not working and the if statement always put to 'FALSE' do you think anything wrong with the code 

View 2 Replies View Related

Sqlserver 2005 Trigger Error

Oct 17, 2007

hi.......
i create a trigger after delete which work perfactly fine on one sqlserver2005 machine but not at another.......machine having sqlserver2005
this trigger effect 15 tables from which i am deleting data...when trigger fires.....
now it cause following error when i fire ir on another machine
error source: .net sql client data provider
error message:maximun stored procedure,triggers,functions,or view nesting level exceeded (limit 32)
plzzzz give da solution.....its really frustrating me....
thanks in advance

View 5 Replies View Related

SQLServer Invokes Trigger Even If There Is No Returned From Insert Subquery

Sep 20, 2007



Please try the following, see that trigger is getting invoked, even if there is no row inserted into trigger table.

drop TABLE X
GO
CREATE TABLE X (
x1 bigint NOT NULL ,
x2 nvarchar(40)
)
GO
drop TABLE Y
CREATE TABLE Y (
y1 bigint NOT NULL ,
y2 nvarchar(40)
)
GO
CREATE TRIGGER trg1
ON X
FOR INSERT AS
BEGIN
DECLARE
@prfirststatus INTEGER,
@newcontextid NCHAR
SELECT @newcontextid = x2,
@prfirststatus = x1
FROM inserted
PRINT 'See the trigger getting invoked without even a single ' +
' row being inserted in table X and values passed to this ' +
' triggers are inserted.x2 = ' + @newcontextid
insert into Y values (@prfirststatus,@newcontextid)
END
GO
insert into X SELECT y1, 'x' from Y where y2 = 'DOESNTEXIST'

View 2 Replies View Related

Class Method Is Smoking Fast When Executed Outside Of SQLServer, Dog Slow As A CLR Function Is SQLServer - Anyone?

May 10, 2007

We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?



Here is the class:






Code Snippet

using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;

namespace Predict.Services
{
public static class Foo
{
public static string GetIntradayQuote(string symbol)
{
string returnQuote = "";

HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));

request.Timeout = 1000;

HttpWebResponse response = (HttpWebResponse)(request.GetResponse());

StreamReader streamReader = new StreamReader(response.GetResponseStream());

returnQuote = streamReader.ReadToEnd();

streamReader.Close();
response.Close();

return returnQuote;
}
}
}



When I run call it from a console app it is fine.



I compile it into a dll and then create the assembly and function as follows:






Code Snippet

drop function fnTestGetIntradayQuoteXML_SJS

go

drop assembly TestGetIntradayQuoteXML_SJS

go

create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS

go

CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote

go



declare @testing nvarchar(max)

set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')

print @testing





When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?



Regards,



Skipper.

View 1 Replies View Related

Problem Unicode Data 0x2300 In SQLServer 2000 SQLServer 2005 Express

Sep 20, 2006

Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))

Insert Data
insert into testunicode (Bez)values('Œ€„¢')
with 2 Unicode characters
Œ€ = 0x2300
„¢ = 0x2122

Selecting the data
select Bez from testunicode
I see
"?„¢"

„¢ = 0x2122 is ok but instead of 0x2300 there is 0x3f

When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'„¢')

and select again voila i see
"Œ€„¢"
Does anyone have an idea?

Thanks

View 1 Replies View Related

Trying To 'load' A Copy Of A SQLServer 2000 Database To SQLServer 2005 Express

Apr 18, 2008



I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.

I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?

The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.

Thanks!!

View 1 Replies View Related

MIcrosft SQLServer Best Practices Document On Securing SQLServer

Jul 29, 2005

I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?

View 2 Replies View Related

Replacing Sqlserver 2000 With Sqlserver 2005 Express

Jun 14, 2006

I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.

Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.

If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:

server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"

DB name: name of db instance

port: 1433(default)

user and pass.

My attempts so far results in

"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."

and

"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."

View 1 Replies View Related

Upgrade SQLServer Mobile (.sdf) Database To SQLServer 2005

Feb 9, 2006

Hello,

I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database

Thanks in advance,

TassosTS

View 1 Replies View Related

SQLSERVER 2005 X64 Linked Server To SQLSERVER 7.0

Jun 20, 2007

Hello people.

I am in the process of planning a server upgrade to sql2005 x64.

I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.

I have the following error when I query the linked servers.
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.

I am aware of KB 906954.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.

However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.

Can someone help me find a solution to this?

View 1 Replies View Related

Post Update For SQLServer SP2--is There One For SQLServer Express?

Apr 18, 2007

Regarding KB935356, is there a "post" service pack 2 update for SQLServer Express?



Thanks.

View 7 Replies View Related

How To Import Access To SQLServer With Parameter From SQLServer, Help Pls!

Jul 8, 2006

Hello Expert!

I have 2 Database €“ Access & SQLServer(ver 7)

I need to Import Data TblShift from Access to SQLServer €“ using DTS I€™ve done this successfully!

Now I want to use parameter so I only importing record within range (e.g. ShiftDate BETWEEN 05-24-2006 AND 06-23-2006)

In SQLServer, I have created table to store the date range as following:

TblParameter
DateFrom: 04/24/2006
DateTo: 05/23/2006

How do I use the date range from TblParameter(SQLServer) to import record from TblShift(Access) using DTS?

Is this possible or any better solution for this?

TIA

Regards,

View 4 Replies View Related

Differences Between Sqlserver 2000 And Sqlserver 7.0

Dec 29, 2005

Hi Friends,
 
Can some please let me know the differences between sqlserver 2000 and sqlserver 7.0

View 1 Replies View Related

Difference In SQLSERVER/sqlExpress And SQLSERVER

Jun 6, 2007

Hi,
I am new to SQL Server 2005. I tried connecting to my local machine by using my machine name as Server name and then tried running the following query:
SELECT * FROM SYS.Objects. It gives me following error:
Invalid object name 'sys.objects'.

Whereas, if I connect to my local machine using mahcinenameSQLEXPRESS, then the above mentioned query runs fine.

Why is this difference? What is the difference when I login in these 2 different ways.
Any help would be appreciated.

Thanks in advance.

Any help

View 1 Replies View Related

CLR-Based Trigger? Recursive Trigger? Common Table Expression?

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

Trouble With Update Trigger Modifying Table Which Fired Trigger

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

Generic Audit Trigger CLR C#(Works When The Trigger Is Attached To Any Table)

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

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

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

Trigger - Require Help For Updating A Trigger Following An INSERT On Another Table

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

How To Transfer Data From A SqlServer Database To A SqlServer Express Database

Mar 29, 2006

Is there a way to transfer data from a SqlServer db to a SqlServer Express db. I tried to use the backup file of SqlServer, but this file is not valid for SqlServer Express. Or there any alternatives?

thanks,

Henk

View 7 Replies View Related

How To Create New CLR Trigger From Existing T-Sql Trigger

Mar 18, 2008

how to create new CLR trigger from existing T-Sql Trigger Thanks  in advance

View 3 Replies View Related

Modifing The Row That Invokes A Trigger From Within That Trigger

Jul 23, 2005

When a row gets modified and it invokes a trigger, we would like to beable to update the row that was modified inside the trigger. This is(basically) how we are doing it now:CREATE TRIGGER trTBL ON TBLFOR UPDATE, INSERT, DELETEasupdate TBLset fld = 'value'from inserted, TBLwhere inserted.id= TBL.id....This work fine but it seems like it could be optimized. Clearly we arehaving to scan the entire table again to update the row. But shouldn'tthe trigger already know which row invoked it. Do we have to scan thetable again for this row or is their some syntax that allows us toupdate the row that invoked the trigger. If not, why. It seems likethis would be a fairly common task. Thanks.

View 4 Replies View Related

Disabilitazione Trigger [DISABLE TRIGGER]

Jul 20, 2005

Salve, non riesco a disabilitare un trigger su sqlserver nè da queryanalyzer, nè da enterprise manager.In pratica tal cosa riuscivo a farla in Oracle con TOAD, mentre qui nonriesco.Mi interessa disattivarlo senza cancellarlo per poi riattivarlo al bisognosenza rilanciare lo script di creazione.Grazie a tuttiHi I need to disable a DB trigger and I'm not able to do this neither withquery analyzer, neither with enterprise manager.I remeber this job was quite simple using TOAd in Oracle.I'm interested in making it disabled not delete it, without run creationscript.Thanks a lot to everybody.

View 4 Replies View Related

Update Trigger Behaviour W/o A Trigger.

May 30, 2008

Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,

View 3 Replies View Related

Drop Trigger With A Variable Trigger Name

Sep 20, 2007



Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers.
The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger

I have the following Trigger

the error is at

DROP TRIGGER @DeleteTrigger

I'm guessing it dosen't like the trigger name being a variable instead of a static name
how do i get around this?

thanks in advance

-- ================================================

-- Template generated from Template Explorer using:

-- Create Trigger (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- See additional Create Trigger templates for more

-- examples of different Trigger statements.

--

-- This block of comments will not be included in

-- the definition of the function.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE TRIGGER RemoveTriggers

ON tblTriggers

AFTER DELETE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Declare @DeleteTrigger as nvarchar(max)

select @DeleteTrigger = TableName FROM DELETED



IF OBJECT_ID (@DeleteTrigger,'TR') IS NOT NULL

DROP TRIGGER @DeleteTrigger

GO

END

GO

View 7 Replies View Related

Trigger Doesn't Log Into The Audit Table If The Column Of Table Has Trigger On Is Null

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

Not Be Able To Reinstall SQLServer 2005 After SQLServer 2005 SP I

May 9, 2006

Hi every body,

I have SQLServer 2005 runs well for months and stop working after install SqlServer2005 SP1. I try to reinstall the SQLServer 2005 but I have problem when install work station component on my and the error is "There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor". Please help me to fix this bug. I do not want to reformat my machine.

John Dang



View 1 Replies View Related

SqlServer

Mar 8, 2007

When I am trying to insert values into a table which is created using Sql Server.
I am gettin Sql Exception : " String or Binary Data would be truncated.The Statement has been terminated. "
Please help me out to trouble shoot this error.
 
With regards
Khasim

View 2 Replies View Related

Sqlserver LOB !!!

Jan 17, 2008

 
Good day ... I need to know how to make a LOB column in sqlserver...
In table creation I definde the column that will receive the binary data as nvarchar(MAX)
the problem now that whenever I try to read it back I get this error
Unable to cast object of type 'System.String' to type 'System.Byte[]'.and when I define the column as varbinary(MAX) it cant real handle large objects
I have a method that will return a DataTable Here's the code that I use to write
DataTable dt = fm.getFileData(fileID);Response.ContentType = dt.Rows[0][4].ToString();Response.BinaryWrite((byte[])dt.Rows[0][2]);
 
Thanks in advance ...

View 2 Replies View Related

Sqlserver

Mar 6, 2008

what are the minimum tables required to many to many relation ship?

View 2 Replies View Related

Vb-Sqlserver

Mar 23, 2002

when i delete the row from VB using transact sql statement
it returns with an error 'TIMEOUT EXPIRED'

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved