Triggers For Beginners

Mar 30, 1999

Hi all,

I have just started getting stuck in to SQL Server and have a silly question - i cannot find an example trigger anywhere for

UPDATE
INSERT
DELETE

I know i can use the insered or deleted tables, but my TSQL doesn't seem to work.

Any assistance (in the form of an exmaple!) would be greatly appreciated.

Thanks in advance

Simon

View 3 Replies


ADVERTISEMENT

Please Help Beginners

Oct 19, 2005

The followong statement is working

iif ([Measures].[Docsum]>500, "1","0")

But there is a need to count these Docsums
so
iif ([Measures].[Docsum]>500, Count[Docsum],"0")

but not this way
Can somebody help and explains, how to Count these sums
Thank's

View 1 Replies View Related

Beginners

Sep 13, 2006

I have just started learning SQL server. Where from I start. I have downloaded SQL Management Studio & SQL Express Edition.

Now What should I do now ?

View 3 Replies View Related

SQL Beginners Problem !

May 20, 2004

*** edited by: master4eva ***
Please enclose your code in < code ></ code> tags (without the spaces). This will make your code easier to read online; therefore, encouraging a response to be faster. It is to your own benefit for your question to be answered in future.

I have already done the editing to include the < code ></ code> tags for this post.
*********

Hi,
I am following tutorials in a book to try and implememnt a basic select query using SQL.
However i am following all the instructions, which includes entering this header:

<%@ Page Language="VB" Explicit="True" Debug="True" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>

Then i am dragging a data grid onto my form, constructing a select query in query builder, however the resultamt code i get is totally different than the one in book.
The ideal poutcome refers to the SQL client, SQL reader etc, however in my code below, there is NO mention of SQL at all...simply dbconnection, IDatareader.

can anyone help me out here..I know it's probably soemthing simple.but I'm stumped, and I've followed everything in the book.

H

Function Page_Load() As System.Data.IDataReader
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=F:My Documents"& _
"shopping.mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "SELECT [Product].* FROM [Product]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function

View 1 Replies View Related

DTS - Beginners Guide

Mar 22, 2001

I have "played" with DTS packages but I am now trying to do the following in an ActiveX VB script

Open a connection to database/table <---- Can't find how to do this!
Read a record from the table
Alter the content based on some rules
Post the record back to the table

Simple eh? If anyone has any examples or can point me to a good source of examples.

Many Thanks.

Geoff.

View 3 Replies View Related

MS SQL Setup For Beginners

May 28, 2004

:) Hi dear fellow members,

I am a newbie to MS SQL. I already completed the installations on the SERVER side and CLIENT side. I have an inventory database in Ms Access that I want to transfer into MS SQL SERVER 2000. Is there anybody who can help me how to do it?

I would be very thankful to anybody who can help like me
who is new to SQL

Regards.

BOBOY PONCE

View 1 Replies View Related

Beginners Question

Jul 23, 2005

This is probably straight forward, but let's see:Say I have a Family which includes an address field table and a relatedFamilyMembers table which includes an age field.I need to output a report on each Family where there is at least onefamily member with an age over 16.Any help gratefully received!

View 4 Replies View Related

Queries Used In Vîdeo For Beginners

Aug 21, 2007

Hi,

I just installed reporting services and now starting with the video tutorial for reporting services(http://msdn2.microsoft.com/en-us/express/aa718391.aspx)
In Video 10 and 11 some queries are used to demonstrate building reports. I would like to repeat the steps in the video but I need the SQL Queries used in those videos.

Does anyone have any idea where I can download those SQL queries in order to programm the same reports as shown in the videos?

Many thanks for your replies and best regards from Switzerland

Chris

View 1 Replies View Related

Resources For ABSOLUTE Beginners

Jan 26, 2007

Seems to me like microsoft mentions learning, but it's never for beginners. I haven't found a single tutorial that says how to make a simple database step by step.

Does anybody know about a tutorial focused on teaching the very basics (a kind of "hello world" application) from setting up the new database with a few sample fields to "deploying" it (making it ready to be used)?

Please let me know.

Once I'm through with these first steps there seems to be a lot of helpful learning resources meant for experienced and expert users, but I'm not there yet...



Thanks,

Bernardo

View 2 Replies View Related

Books For Sql Server Development Beginners

Dec 22, 2007

Hi,

Can anyone suggest me good books for learning sql server development ie stored procedures etc..

View 1 Replies View Related

Connect To SQL Server 2000 With VB.NET For Beginners

Sep 4, 2006

I just started to learn Visual Basic 2005.NET. I have experience with VBA in Access and SQL Server.
I
now want to build applications (client/server) with VB & SQL
Server. Does anyone have a simple example with which I can start
learning?

Thank you in advance!

View 4 Replies View Related

Can't Play Files: Video Series: SQL Server 2005 Express Edition For Beginners

Apr 20, 2006

I cannot play any of these video files in RealPlayer or in Windows Media Player. Any suggestions? Anyone else having this problem?

Thanks,

Brian

Video Series: SQL Server 2005 Express Edition for Beginners

C00D1199: Cannot play the file

View 6 Replies View Related

Multiple Triggers On A Table Or Encapsulated Triggers

May 12, 2008

This isn€™t an problem as such, it€™s more of a debate.

If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.

www.handleysonline.com

View 12 Replies View Related

Triggers

Jan 3, 2007

Is it possible to achieve this using triggers:When someone tries to delete a row in table A, the trigger should first delete a corresponding row in table B and then delete the row in table A. The reason being that, there is a foreign key set on Table B that references table A. So any attempt to delete a row in table A without deleting the corresponding row from B, throws an error. 

View 1 Replies View Related

Triggers

May 31, 2007

Hello All,
I have to write Trigger for Update,
I have two tables, one is for storing records of current values, and one is for storing history of values.
How to Write a Trigger on Main Table. As we have Inserted and Deleted Tables through which we can find Values, We dont have any Table for UPDATED Values.
 Help me.
 General Problem
 

View 1 Replies View Related

Triggers - Is This Possible?

Jun 15, 2007

I need to create a set of rows every time a new row is inserted into a table.
Example (I think this would work)...
select @insertedId = column1 from insertedselect @id = column1 from table1 where column2 in (select column1 from table2 where column2 = @insertedId)insert into table3 values(x, y, @id)
Is it possible to do the same kind of thing in a situation where the select statement returns multiple values and execute the insert statement for each of these values?
Also, if table3 was in fact the table on which the trigger acts, would it then be executed for every row created by the trigger?
Sorry if I sound confused. I am.

View 2 Replies View Related

Triggers

Dec 6, 2007

 
 hi everybody..i tried to put thios loop in sql server 2000 But it is not taking The @ action taken value ,,it is only taking the default value of @actiontaken value.
SET @ActionTaken = 'A' 
IF (@AType = 'A')IF @Status= 'O' IF (@KAppInd ='Y' AND @DAppInd=null)BEGINSET @ActionTaken = 'O'END
 
Please tell me other option in sql server 2000 for setting variable value based on conditions

View 3 Replies View Related

Triggers In .net

Dec 21, 2007

Hi using triggers i try to insert some values in to my 2 tables: But its showing teh error as "The request for procedure 'Triginsert123s' failed because 'Triginsert123s' is a trigger object." This is my code in back end: sqlcon.Open() Dim cmd As New SqlCommand("Triginsert123s '" & txtID.Text & "','" & txtName.Text & "','" & txtRole.Text & "','" & txtDep.Text & "'", sqlcon) cmd.ExecuteNonQuery() sqlcon.Close() My trigger is: CREATE TRIGGER Triginsert123s ON [dbo].[EmpRole] FOR INSERT AS declare @Eid as tinyint, @Ename as varchar(50), @Role as char(10) Insert into Emprole(Eid,Ename,Role) values(@Eid,@Ename,@Role) insert into empdep(eid,dep) values(@eid,@Role) Whats the probs?, Plz i am new to triggers help me,

View 3 Replies View Related

Triggers

May 9, 2004

Hi All,
I'm using triggers to handle my transaction log to cature inserts and updates. It works fine except if the user clicks on the Save button more than once, the trigger is fired and the record is written to the log even if the record wasn't changed. Does anyone know how to check if the record was actually changed so that it isn't written to the table if it wasn't?

Thank you,
Mike

View 3 Replies View Related

SQL Triggers

Jun 17, 2004

When I execute a stored proc from my asp.net page, will the results of a trigger be returned to my program?

For instance say my stored proc is:

Update Employees set
(Lastname = @Lastname)
where ID = @ID

And my trigger is:

CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS
IF UPDATE(lastname)
BEGIN
RAISERROR ('cannot change lastname', 16, 1)
ROLLBACK TRAN
RETURN
END
GO


It seems like since this is an AFTER trigger that my webpage would actually get a valid return code from my stored procedure however the trigger would rollback those changes correct? Or would the trigger get fired and send it's return code to my webpage?

View 10 Replies View Related

Instead Of Triggers

Mar 5, 2006

I'm trying to write an instead of trigger for a view in SqlExpress...the table and views are defined as such:CREATE TABLE [dbo].[Work](    [WorkID] [int] IDENTITY(1,1) Primary Key,    [ResourceID] [int] NOT NULL,    [TaskID] [int] NOT NULL,    [WorkDate] [datetime] NOT NULL,    [WorkQuantity] [float] NOT NULL,    [IsEstimate] [bit] NOT NULL DEFAULT ((0)),    [Project] [int] NOT NULL,);CREATE VIEW [dbo].[ActualWork]ASSELECT     WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM         dbo.[Work]WHERE     (IsEstimate = 0);CREATE VIEW [dbo].[EstimatedWork]ASSELECT     WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM         dbo.[Work]WHERE     (IsEstimate = 1);Given that, what is wrong with the following create trigger statement:Create Trigger trg_InsertActualWork ondbo.ActualWork Instead of InsertasBEGIN    Insert into dbo.Work(        ResourceID, TaskID, Project,         WorkDate, WorkQuantity, IsEstimate    )    values (    inserted.ResourceID, inserted.TaskID, inserted.Project,    inserted.WorkDate, inserted.WorkQuantity, 0    );END

View 3 Replies View Related

Triggers

Dec 4, 2001

l'm trying to build a trigger on a table. The reason for the trigger is to check a certain field for the first three characters if it has ie abc it must update another field in this case loanbook to newabc.How do l write the trigger so that it also check if exists and perform the updates. Please help its Urgent. l've listed the trigger below.

CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR insert,Update AS

IF left('Loan_No',3)='ABC'
update Test_TRG
set loanbook = 'NEWABC'

else

IF left('Loan_No',3)='DEF'
update Test_TRG set
loanbook = 'NEWDEF'
where loanbook is null

else

update Test_TRG
set loanbook =left('Loan_No',3)
where loanbook is null

View 4 Replies View Related

Triggers

Dec 5, 2001

I'm a bit confused on this bit please elaborate : " FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK ".
The PK is on which field? Basically this trigger should ensure that on insertion of o new loan if
The left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=KVS and its null then NEWKVS
left(loan_no,3)=MFS and its null then MFS
left(loan_no,3)=TCR and its null then TCR
left(loan_no,3)=ABL and its null then ABL

Listed below is what l've tried to do but l'm missing the PK part.Otherwise everything else you explained
in the script is clear. Thanks man its urgent. When l parse the query its fine , but When l run it
l get an error.



CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR INSERT, UPDATE
AS

UPDATE Test_TRG
SET LoanBook = CASE WHEN LEFT( i.Loan_No, 3 ) = 'MCG' THEN 'NewMCG'
WHEN LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL THEN 'NewKVS'
WHEN LEFT( i.Loan_No, 3 ) = 'MFS' AND i.LoanBook IS NULL THEN 'MFS'
WHEN LEFT( i.Loan_No, 3 ) = 'ABL' AND i.LoanBook IS NULL THEN 'ABL'

WHEN i.LoanBook IS NULL THEN LEFT( i.Loan_No, 3 )
END
FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK
-- Fill in Primary Key or other Join Column(s)
WHERE LEFT( i.Loan_No, 3 ) = 'MCG'
OR ( LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL )
OR i.LoanBook IS NULL

====== Error message =================

Server: Msg 207, Level 16, State 3, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.
Server: Msg 207, Level 16, State 1, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.

View 1 Replies View Related

Triggers

Jan 11, 2002

I an loading records from a flat file into a table, which is done everyday by a scheduled job in SQL Server 7.0.

How can I make sure that if the job is run twice in a day for some reason that the same rows are not inserted into the table again? Do I have to write a insert trigger on the table ??? If so how can I achive the objective ??

View 1 Replies View Related

Triggers

Jan 31, 2002

Hi! i have a simple question...is possible to create a trigger who affect multiple tables?
The idea will be something like this:

create trigger mytrigger
on sales, users
as...

of course, this don´t work :)

View 1 Replies View Related

Triggers

Aug 3, 2001

Hey everyone...

I'm working on trying to figure out how to update the child table from the parent via a trigger. This works fine as long as the value is in the child table. If it's not, then I get my foreign key violation. So, My next thought was to simply put in a begin tran / rollback tran within the trigger... My question is this : If I have multiple triggers on the parent table all based on the update of the key field (and the child tables... some 7 of them) all have FK's, if I rollback one trigger does it rollback the entire transaction? Or just the functionality of that trigger? From what I've read, it appears as if it's the entire transaction... if so, how do I get a trigger to 'ignore' itself or not fire based on a select criteria that i have within that same trigger...

Thank you...

View 3 Replies View Related

Triggers

Oct 24, 2001

I just tried creating my first trigger and I'm a little bit confused. I am trying to see if certain fields have changed and will do that by checking the values of the fields in the "master" table and the "Inserted" table. I wasn't sure which table would have the old data and which would have the new, so I created the following trigger to help me determine which was which:

CREATE Trigger upd_cost_master On tbl_cost_master
For Insert, Update As

Declare @U_Name varchar(65)
Declare @O_Name varchar(65)

Select @U_Name=I.submitted_by, @O_Name=O.submitted_by
From tbl_cost_master O Inner Join Inserted I On O.autokey = I.autokey

Print 'Inserted: ' + @U_Name
Print 'tbl_cost_master: ' + @O_Name

Commit Tran

To my surprise, @U_Name and @O_Name contained the same values. I though that one table contained the old values and one contained the new. Was I wrong? If so, is there anyway to compare the Old and New values through a trigger?

Chris

View 1 Replies View Related

Triggers....

Jul 20, 2000

Hi,

I'm using SQL Server 7.0. I have an insert trigger on table t05r. I populate table t05r with a DTS package. The trigger is supposed to add the record which was inserted into t05r, into t39r. After I run the DTS package to populate t05r, I checked t39r and no records where added. What is going wrong? Is it that DTS uses bulk copy and triggers don't fire on bulk copy?

Any help would be greatly appreciated.

Thanks in advance,
Darrin

View 1 Replies View Related

Triggers

Nov 1, 1999

I have an insert trigger on table A which inserts records into table B.
This insertion into B fails( or may fail ) at certain conditions.
I do not want the insertion into A to be affected because of this. Any ways to achieve this ?

View 1 Replies View Related

Triggers

Oct 8, 1999

Hi,
I have 3 tables A,B C and Triggers on each table like.

for insert/update on table 'C' calculates sum(xxfield) and updates
table 'B'.

for insert/update on table 'B' calculates sum(yyfield) and updates
table 'A'.

This works fine if I insert rows OneByOne.
Problem :- Doesn't work if I post entries(hundreds) in to table 'C' using a Query.

whats happening. Can anyone help Me????????

View 1 Replies View Related

TRIGGERS

Nov 12, 2001

How do I know if the triggers on table has been disabled or not. Thanks for your ideas!!!!

View 3 Replies View Related

Triggers Off

Nov 21, 2002

Hello,

I've update triggers on my table, But for some of the transactions, I don't want the triggers to fire. I know in Sybase there are set triggers off. In MS SQL, Is there any similar commands which will set triggers off for particular sql statements.

Thanks A Lot

Sejal

View 2 Replies View Related

Triggers In SQL

May 1, 2003

Hi! I am having trouble with setting triggers while doing my project. The project is on ATM machine driven through SQL. So I have Date of Transaction in one table and Account Expiry in another.

I want to set the trigger to check Account expiry when doing transaction so i wrote T-SQL

select........
getDate()-Dateoftransaction=expiry
begin '...'
end

the system saying I have got an error near the word 'begin'.

I am desperately needing help. running out of ideas.

Thanks in advance.

View 1 Replies View Related







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