Why Does SQL Freeze When I Run This Trigger???

Jun 13, 2002

CREATE TRIGGER accountI ON [dbo].[account]
FOR UPDATE
AS
declare @a decimal
declare @b varchar
declare @c varchar
declare @d decimal
declare @e decimal
declare @f decimal
declare @g decimal
declare @h decimal
declare @i decimal
declare @j decimal
declare @k decimal
declare @l decimal
declare @m decimal
declare @n decimal
declare @o decimal
declare @p decimal

Declare tblCursor Cursor for
select begBalance,accountDescription, accountType from account
where accountType = 'Cash' or accountType = 'Accounts Receivable' or accountType = 'Inventory' or accountType = 'Other Current Asset'
open tblCursor
Fetch Next From tblCursor INTO @a,@b,@c
While (@@Fetch_STATUS = 0)
begin
select @d = @d + @a
end
Fetch Next From tblCursor INTO @a,@b,@c

Declare tblCursor Cursor for
select begBalance,accountDescription, accountType from account
where accountType = 'Fixed Asset' or accountType = 'Accumulated Depreciation'
open tblCursor
Fetch Next From tblCursor INTO @a,@b,@c
While (@@Fetch_STATUS = 0)
begin
select @e = @d + @a
end
Fetch Next From tblCursor INTO @a,@b,@c


Declare tblCursor Cursor for
select begBalance,accountDescription, accountType from account
where accountType = 'Other Asset'
open tblCursor
Fetch Next From tblCursor INTO @a,@b,@c
While (@@Fetch_STATUS = 0)
begin
select @f = @d + @a
end
Fetch Next From tblCursor INTO @a,@b,@c


Declare tblCursor Cursor for
select begBalance,accountDescription, accountType from account
where accountType = 'Accounts Payable' or accountType = 'Other Current Liabilities'
open tblCursor
Fetch Next From tblCursor INTO @a,@b,@c
While (@@Fetch_STATUS = 0)
begin
select @g = @d + @a
end
Fetch Next From tblCursorr INTO @a,@b,@c

Declare tblCursor Cursor for
select begBalance,accountDescription, accountType from account
where accountType = 'Long-Term Liabilities'
open tblCursor
Fetch Next From tblCursor INTO @a,@b,@c
While (@@Fetch_STATUS = 0)
begin
select @h = @d + @a
end
Fetch Next From tblCursor INTO @a,@b,@c

Declare tblCursor Cursor for
select begBalance,accountDescription, accountType from account
where accountType = 'Equity-Retained Earnings' or accountType = 'Equity-Not Closed' or accountType = 'Equity-For Closure' or accountType = 'Income'
open tblCursor
Fetch Next From tblCursor INTO @a,@b,@c
While (@@Fetch_STATUS = 0)
begin
select @i = @d + @a
end
Fetch Next From tblCursor INTO @a,@b,@c


Declare tblCursor Cursor for
select begBalance,accountDescription, accountType from account
where accountType = 'cogs'
open tblCursor
Fetch Next From tblCursor INTO @a,@b,@c
While (@@Fetch_STATUS = 0)
begin
select @j = @d + @a
end
Fetch Next From tblCursor INTO @a,@b,@c


Declare tblCursor Cursor for
select begBalance,accountDescription, accountType from account
where accountType = 'expenses'
open tblCursor
Fetch Next From Stock_Cursor INTO @a,@b,@c
While (@@Fetch_STATUS = 0)
begin
select @k = @d + @a
end
Fetch Next From tblCursor INTO @a,@b,@c


insert dbo.financials (totalCurrentAssets, totalPropEquip, totalOtherAssets, totalCurrentLiabilities, totalLongTermLiabilities, totalEquity, totalIncome, totalCogs, totalExpenses, totalAssets, totalLiabilitiesEquity, totalNetSales, totalNetIncome)values (@d, @e, @f, @g, @h, @i, @j, @k, @l, @m, @n, @o, @p)

View 4 Replies


ADVERTISEMENT

System Freeze

Jan 2, 2000

I am running NT4.0 with sp6 and SQL server 6.5 with sp3 and my system periodically freeze. The CPU shows 100% and Sql does not respond. In the NT event viewer I get a message SQLCTR60 DB-LIBRARY error - SQL Server connection timed out. How do I go about finding out what is wrong.

Thanks in advance!

Betty

View 2 Replies View Related

Freeze Panes

Apr 23, 2008

Good Morning,

I want to freeze my colum headings which are in the table header section, can this be done?

Thanks,
Rhonda

View 1 Replies View Related

Freeze Panes?

Jan 30, 2006

Is there functionality within the Table or Matrix objects to freeze columns or row headers when you scroll thru a wide or long report? Similay to the freeze panes functionality in Excel.



Thanks

View 12 Replies View Related

All 32bit Connections Freeze...

Mar 2, 1999

I'm very new to SQL servers. We're running SQL 6.5-SP4 and have three applications using different databases on the server (NT4-SP3).

App 1. Written in PowerBuilder using 32bit DB_LIB, NWLink IPX/SPX.
App 2. Written in VFP 5.0a using ODBC32, NWLink IPX/SPX
App 3. Written in VB using ODBC16, NWLink IPX/SPX

Something happens, which freezes all 32bit connections and prevents any other 32bit connects. Which means I can't get Enterprise Manager to connect! This does not affect the ODBC16 clients?!?!

So far the only way, I have found, to clear and fix is to get all 32bit users out of their apps and the server becomes available again. There's got to be something to prevent this, any ideas? Is there a way to trace this to find the culprit?

Thank you for any and all help,

Jim
<jpeppercorn@jhcb.com>

View 3 Replies View Related

Freeze An Index While Updating

Sep 20, 2007

Her is what I'd like to do:

- Create and populate a table
- Index it on a 'reference' field
- Then update the table by using the index but WITHOUT updating the index (I'd like to have it available for the WHERE clause but I don't need to updtae it)
- Eventually, i'd like to re-index the whole table

Is that possible?

Thank you for your help...

View 2 Replies View Related

SQL Server Freeze And Thaw Functions

Aug 20, 2007

Hi,
I saw in some websites that there are functions freeze and thaw in SQL server.I want to freeze the SQL server for some time and then use the thaw to unfreeze.I want to know how it could be done in SQL server 2005

Thanks in Advance

View 5 Replies View Related

Validating Sequence Container Freeze

Feb 16, 2007

I am attempting to open up my ssis integration project but when I try to open up packages it says @ the bottom "Validating Sequence Container" and it just stays there. I cannot click on anything and nothing is responsive. Does anyone know why this is happening?

View 9 Replies View Related

Can I Freeze The Leftmost 2 Columns When Scrolling Horizontally?

Mar 26, 2007

Hello,

I have a very wide report that uses a table control. I would like to "freeze" the two leftmost columns so that they remain visible when the user is scrolling horizontally (sideways). Is this possibe?

Thanks,

BCB

View 8 Replies View Related

Reporting Services :: Freeze Header Browser

Apr 22, 2015

I want the header to be visible while scrolling. After changing on the static Properties FixedData=True, it is working on BIDS and Internet Explorer 11 . But is does not work in Chrome. Most of my users are using Chrome. Is there any way to fix it for google Chrome.

View 2 Replies View Related

Sql Server 2005 Machine Lockup/freeze Up

Mar 25, 2008


Our HP Proliant ML 330 G5 (3.0 Duo Core Xeon, 4 GBs RAM) server is running Windows 2003 R2 Standard 32 bit ( Win SP2) and SQL Server 2005 Standard (SQL SP2 €“ version 9.00.3054.00(Intel X86)). At random times every 1-14 days, our server will lock up. While the Windows Login screen or the standard Windows screen saver is viewable on the server still, the computer becomes completely non-responsive. The keyboard and mouse do not work and we can no longer connect to the server via a Terminal Service connection. We are able to ping the server while it is locked up though. The only way to get the server back up and running is by doing a hard power down and then power it back up.

Upon getting the server back up and running, everything goes back to working as normal with both Windows and SQL. There is nothing in any of the event logs indicating any type of issue prior to the server locking up. We are really at a loss on where to start troubleshooting this. We have no reason to think it is a hardware issue at this point but are not ruling that out.
Any ideas what we can do to start tracking down the cause of this problem?

View 3 Replies View Related

Count Up Based On Unique Value -- Brain Freeze

Apr 17, 2008



Ok... I know this is something that I've seen and probably done before....

I have a value that will show up multiple times in a table. I want to order the table by that value and then count up from 1 to however many items that it shows up as.

The example below shows the results I want. Value1 and Value2 are in the table, LineNumber is what I need to generate.








Value1
Value2
LineNumber

1
Hey
1

1
Can
2

1
Someone
3

1
Help
4

1
With
5

1
This
6

2
Very
1

2
Simple
2

2
Issue
3

2
As
4

2
I
5

3
Have
1

3
A
2

3
Brain
3

3
Freeze
4



--Thanks--

View 4 Replies View Related

I Need Help With Cleaning A Phone Number Column, Brain Freeze

Feb 26, 2002

For anyone who can help me resolve this minor data issue. I am trying to clean a phone number column so that all numbers are reflected in the same way. I have phone numbers in my column in this this format:

(123)123-1234
123-123-1234
(123) 123-1234

How can I clean this column so that the numbers are reflected in this way: 1231231234

I'm having brain freez right now. Help me please!!! Thanks in Advance

View 2 Replies View Related

Reporting Services :: Freeze Headers While Exporting Report To Excel?

Sep 17, 2010

How can i freeze the headers while exporting the report to excel? will this works in ssrs 2008 / 2008 r2?

View 2 Replies View Related

Transact SQL :: Adding Database - Single User Mode Freeze

Oct 5, 2015

I would like to add database to single user mode to enable broker. So, i have tried this:

ALTER DATABASE myDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE myDb SET ENABLE_BROKER
ALTER DATABASE myDb SET MULTI_USER

But the query works more than half an hour then i have stopped it. i guess it has infinite loop, I get also many of this messages:

Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.

There is no other transaction also there is no other user connections. So, what happens?

View 5 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 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

Trigger Not Execute Some Data Or Insert Not Execute A Trigger For Some Data

Mar 3, 2008

I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data.
For example 100 data every second inserted to a table.

If insert data one by one to a table trigger fires success.
Please Help me.

View 1 Replies View Related

Sp Name In Trigger

Jun 17, 2006

There are two different Stored Procedures insert data into a table, is it possible to catch what stored procedure name was that trigged the trigger in the trigger?

View 1 Replies View Related

SQL Trigger .. Please Help...

Jul 26, 2006

Hi, I am trying to script a trigger on a table to accomplish the following... If a row is updated then the value in the 'date' column in that specific row would update to getDate().
I have never created a trigger before and finding it very difficult. Thank you very much in advance.
Kind Regards,
Sean.

View 6 Replies View Related

Trigger Ignored

Feb 4, 2007

Hello
I am having difficulty try to get a trigger to work.
I need to create a trigger querie that will insert a row from TableMain (Column LNames) table to TableA (Column LNames)
Below is my querie
 
CREATE TRIGGER [trig_addTable_A]ON Table_A FOR  INSERT     ASINSERT LName (LName)
SELECT LNameFROM TableMain inserted
 I have inserted test names into the main table but nothing in table A has inserted.  What have I done wrong.
Thanks
 
 

View 6 Replies View Related

SQL Trigger

Mar 11, 2007

I am looking for a INSERT trigger example, to achieve the following objective:
 I would like the trigger to be fired when a row gets inserted into a table. I want to retrive some value from the inserted row and use this value to insert a new row in table 2.
 Thanks
 
chri
 

View 4 Replies View Related

About Trigger

Mar 17, 2007

is it possible to run a trigger forever... whose work is to archive data from a table. or  how can i do the operation to archive automatically ?Regards,Kamrul Hassan 

View 1 Replies View Related

For Each Row Trigger

Aug 14, 2007

Hi,
Does anybody know how could I define a for each row trigger in sql Server 2005?
What I need to do is before inserting in the table look through the rows to check wheter that value overlaps with the others.
"Overlap" means that this value cannot be betweent the values fo two different columns, that is why I need to go through all the table.
In case that was not possible, I propose the following, load the table in a dataset and check the constraint with a for loop despite of the performance could be decreased.
Any suggestion?.
 
Thanks in advance.
Cheers.

View 8 Replies View Related







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