Loop To Run 'Create Trigger' Script?
Jul 17, 2006
I need to run a script to create a trigger on 18 tables on 3 databases. The code is identical with the exception of the table and trigger names at the beginning. Does anyone know of a way to create them all with a loop instead of manually replacing the table and trigger names and executing over and over? I tried variables but get an 'Incorrect syntax near '@TriggerName' error.
if exists (select * from sysobjects where id =
object_id (N'dbo.tgUsersAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgUsersAudit
go
CREATE TRIGGER tgUsersAudit on tblUsers FOR insert, update, delete
AS
DECLARE @TableName varchar(128)
SET @TableName = tblUsers
..................from here the code is the same for all
View 11 Replies
ADVERTISEMENT
Jun 4, 2007
Hello all
I've been thrown in at the deep end by the manager and asked to use a SQL database for a new site. I've managed to get most of the code working but are having trouble with this particular one.
The idea is that a user can log a fault (online) and that this gets added to a table. Once the data is added a trigger fires that takes this new data and loops through an "e-mail alert" table containing settings for different department managers and then stores the relevant manager name in a separate table (e-mail sent fromt this table to the manager) once the department and fault id's match.
I have the following code (which is by no means good) in my trigger - did I says this is my first outing into SQL land!!ALTER TRIGGER [dbo].[usd_emailalertqueue]
ON [dbo].[FaultPostings] AFTER INSERT
AS
-- declare variables from new fault table
DECLARE @iDeptID intDECLARE @iFaultID intDECLARE @iReferenceID int
-- declare variables from email alerts tableDECLARE @Managername nvarchar(255)DECLARE @DeptID intDECLARE @FaultID intDECLARE @ReferenceID int
-- Read in values new fault tableSELECT @sParam = FaultDescription FROM INSERTEDSELECT @iFaultID = FaultID FROM INSERTEDSELECT @iDeptID = DeptID FROM INSERTEDSELECT @iReferenceID = ReferenceID FROM INSERTED
BEGIN
-- Read values from saved e-alertsSELECT @Managername = Managername FROM [dbo].[emailAlerts]SELECT @DeptID = DeptID FROM [dbo].[emailAlerts]SELECT @FaultID = FaultID FROM [dbo].[emailAlerts]
INSERT INTO EmailAlertQueue(ReferenceID,Managername)SELECT @iReferenceID, @Managername
FROM [dbo].[emailAlerts] WHERE (@iDeptID = @Deptid) AND (@iFaultID = @Faultid) then END
At the moment, this is what happens when a fault is added: the EmailAlertQueue table gets populated with the name of the last managername from the emailAlerts table several times (only if the Dept and Fault id's macth otherwise nothing gets populated.. e.g. if there at 4 alerts setup in the EmailAlerts table with different dept and fault ID's but the last one matches the criteria the emailAlerts table is uotaed with 4 rows containing the managername. (Hope this makes sense).
I think I need to have some sort of loop but so far as I can tell a "For Each" loop doesn't work in triggers (i could be wrong though).
(I'm not even sure if this is the correct approach to take)
As usual, any help is appreciated.
Thanks
Chooch
View 3 Replies
View Related
Jul 9, 2007
Hi,Right, I have this problem, and it;s more through lack of understanding vb.net that well more then an actual problem I will out line what I want to do,basically it all revolves around a page that needs to be built when navigated to so it can be easily updated without anyone having to edit the code.Get all the table names from a databaseLoop through each of the results to build a statementNest a 2nd loop to split the returned data from the correct tableBuild a listbox for each table returnedThis is what I have currently, this works but the problem is, if another course is added, someone will need to manually edit the code on the page to add a new code to get the new course hence why I want to create a loop that gets all the data so all someone needs to do is put in the all table the new course name. Please noteI cut this down to just show 2 result but there is about 30 odd. 1 DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=xx")
2 'Dim DBDataAdapter As SqlDataAdapter
3 DBDataAdapter = New SqlDataAdapter("Select AOE,ADC, FROM TBL_Role WHERE Title = @ddlTitle", DBConn)
4 DBDataAdapter.SelectCommand.Parameters.Add("@ddlTitle", SqlDbType.NVarChar)
5 DBDataAdapter.SelectCommand.Parameters("@ddlTitle").Value = TitleDropDown.SelectedValue
6 DBDataAdapter.Fill(DSPageData, "Courses")
7
8 'Loop through each record, split at + and place in ListBoxs
9
10 VarDS = DSPageData.Tables("Courses").Rows(0).Item("AOE")
11 Dim VarArray As String() = VarDS.Split("+")
12 Dim i As Integer
13 For i = 0 To VarArray.Length - 1
14
15 Dim li As New ListItem()
16 li.Text = VarArray(i).ToString()
17 li.Value = VarArray(i).ToString()
18 Me.txtAOE.Items.Add(li)
19 Next i
20
21 VarDS = DSPageData.Tables("Courses").Rows(0).Item("ADC")
22 VarArray = Nothing
23 VarArray = VarDS.Split("+")
24 i = Nothing
25 For i = 0 To VarArray.Length - 1
26
27 Dim li As New ListItem()
28 li.Text = VarArray(i).ToString()
29 li.Value = VarArray(i).ToString()
30 Me.txtADC.Items.Add(li)
31 Next i
Now here is my pseudo code to what I roughly want to do, hope it makes sense to someone and someone can point me in the correct direction. Please note,I know the split bit works, so at the minute I am just trying to get the loop to get all my courses 1 DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=wgw;PWD=wgwsql;")
2 DBSelect.Connection = DBConn
3 DBSelect.Connection.Open()
4 'Get the row number in the database
5 DBSelect.CommandText = "SELECT COUNT(*) FROM TBL_All"
6 DBResult = DBSelect.ExecuteScalar()
7 DBSelect.Connection.Close()
8 Dim Count = DBResult
9 'Get all the Tables and Keys in the Database's
10 DBDataAdapter = New SqlDataAdapter("SELECT * FROM TBL_All", DBConn)
11 DBDataAdapter.Fill(DSPageData, "All")
12 'declare all loop vars
13 Dim X As Integer
14 Dim Y As Integer
15 Dim i As Integer
16 'Loops through all the tables
17 Dim DSArray As String() = DSPageData.Tables("All").Items()
18 For Y = 0 To Count
19 Dim VarDS As String() = DSPageData.Tables("All").Rows(0).Item(DSArray(Y))
20 Dim SplitArray As String() = VarDS.Split("+")
21
22
23 For i = 0 To SplitArray.Length - 1
24 Dim Li As New ListItem()
25 Li.Text = SplitArray(i).ToString()
26 Li.Value = SplitArray(i).ToString()
27 Me.txt & DSArray(Y) &.Items.Add(Li)
28 Next i
29
30 Next Y
View 3 Replies
View Related
Mar 18, 2008
how to create new CLR trigger from existing T-Sql Trigger Thanks in advance
View 3 Replies
View Related
Jun 28, 2007
I have a basic while loop but I need to be able to increment the counter from one alpha character to the next:
declare @counter nvarchar(10)
set @counter = 'A'
while @counter < 'Z'
beginprint 'the counter is ' + @counter
set @counter = @counter + @counter
end
In this example I would need to see the following output:
the counter is Athe counter is Bthe counter is Cthe counter is D.....
Is this possible in SQL?
View 2 Replies
View Related
Oct 25, 2007
Hey
i have a table A that contains 3 columns : id, entry ,sessionid
i want to create a view on this table that will contain
- for each sessionid s in A --> select top 5 rows having s as sessionid and ordered by id desc
(s can have 1 or 2 or 5 or 300 entries i want to get only the latest 5 rows that correspond to this session)
I tried many queries and different combinations i could find one yet to do the following.
Can anyone help me plz?
Can we have a loop in a view?is it possible?
View 7 Replies
View Related
May 29, 2008
I have a stored proc I am running, and I would like to create a list of email addresses from a table and put that list into a variable. I did a basic while loop to work on syntax, but now I don't know how to actually get each address added on. Here's how I started it
declare @start int, @testEmail nvarchar(2000)
set @start = 1
set @testEmail = NULL
while @start <= (Select count(PADM_Email) from PADM_Emails)
Begin
--Print @start
set @testEmail = @testEmail + (Select distinct PADM_Email from PADM_Emails) + ';'
set @start = @start + 1
End
I know that the above is wrong, but I don't know how to get it right. Ideally, I want the @testEmail to look like this:
emailaddress1;emailaddress2;emailaddress3;
View 7 Replies
View Related
Jun 19, 2008
Hi all
I'm new to sql and could do with some help resolving this issue.
My problem is as follows,
I have two tables a BomHeaders table and a BomComponents table which consists of all the components of the boms in the BomHeaders table.
The structure of BOMs means that BOMs reference BOMs within themselves and can potentially go down many levels:
In a simple form it would look like this:
LevelRef: BomA
1component A
1component B
1Bom D
1component C
What i would like to do is potentially create a temporary table which uses the BomReference as a parameter and will loop through the records and bring me back every component from every level
Which would in its simplest form look something like this
LevelRef: BomA
1......component A
1......component B
1......Bom D
2.........Component A
2.........Component C
2.........Bom C
3............Component F
3............Component Z
1......component C
I would like to report against this table on a regular basis for specific BomReferences and although I know some basic SQL this is a little more than at this point in time i'm capable of so any help or advice on the best method of tackling this problem would be greatly appreciated.
also i've created a bit of a diagram just in case my ideas weren't conveyed accurately.
Bill Shankley
View 4 Replies
View Related
Apr 24, 2007
What I'm trying to do is this;
I have a table with Year , Account and Amount as fields. I want to
SELECT Year, Account, sum(Amount) AS Amt
FROM GLTable
WHERE Year <= varYear
varYear being a variable which is each year from a query
SELECT Distinct Year FROM GLTable
My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.
What I have done is to defined two variables as follows
Name: varYear (this will hold the year)
Scope: Package
Data type: String
Name:vSQL (This will hold a SQL statement using the varYear)
Scope: Package
Data type: String
Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]
I've created a SQL Task as follows
Result set: Full Result Set
Connection Type: OLE DB
SQL Statement: SELECT DISTINCT Year FROM GLTable
Result Name: 0
Variable Name: User::varYear
Next I created a For Each Loop container with the following parameters
Enumerator: Foreach ADO Enumerator
ADO Object source Variable: User::varYear
Enumeration Mode: Rows in First Table
I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows
Data Access Mode: SQL Command from Variable
Variable Name: User::varYear
However this returns a couple of errors "Statement(s) could not be prepared."
and "Incorrect syntax near '='.".
I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.
Any help would be most appreciated.
Regards,
Bill
View 5 Replies
View Related
Jun 4, 2014
I have a for each loop(ADO Enumerator) container which executes for each Advertiserid which is coming from database. In for each loop I have to create a new excel file with the advertiser name. So if the loop executes 7 times there should be seven excel spreadsheets with seven advertiser names.
How can i create an excel dynamically in the foreach loop container.
View 10 Replies
View Related
Oct 25, 2007
I am looking for the best way in SSIS to do the following. I have an SQL table that for each row in the table I want to take an element from the table do a lookup in a Teredata Table, return information from the teredata source. Use that returned data to do some calculations and create a derived column from my calculations and place the data into the same SQL table that I am parsing through.
Ideas?
View 1 Replies
View Related
Sep 14, 2004
I have a table that has a number of data fields,I need to be able to capture datatime when the date field was entered or entered value changed.I was told I need to create a trigger on that table that contains all the fields.
I have seen the syntax for creating triggers, and read some documentation but I am still in the dark as how to create what I need to.
I was hoping to see if somebody had a similar example or an advice, anything is more than what I have at the moment.
CREATE TRIGGER NotifyDateFieldUpdates
ON RelocateeRemovalist
For INSERT, UPDATE, DELETE
AS
DECLARE @RemovalistNumber VARCHAR(200)
DECLARE @RelocateID INT
/*InspectionDate */
DECLARE getInsp CURSOR FOR SELECT RelocateID,RemovalistNumber
FROM INSERTED a LEFT JOIN DELETED b ON (a.RemovalistNumber=b.RemovalistNumber and a.RelocateID=b.RelocateID)
WHERE a.InspectionDate IS NOT NULL AND b.InspectionDate IS NULL
OPEN getInsp
FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber
WHILE (@@FETCH_STATUS <> -1)
BEGIN
INSERT INTO RelocateeRemovalistFieldEntry(RElocateID, RemovalistID)SELECT
RelocateID,RemovalistID FROM INSERTED a LEFT JOIN RelocateeRemovalistFieldEntry b ON
(a.RelocateID=b.RelocateID AND a.RemovalistNumber=b.RemovalistNumber)WHERE b.RElocateID is null
UPDATE RelocateeRemovalistFieldEntry SET InspectionDateDateTime=GETDATE()
WHERE RelocateID=@RelocateID aND RemovalistNumber=@RemovalistNumber
FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber
END
DEALLOCATE getInsp
GO
This is what I was able to come up with so far,but when i check the syntax it gives me an error "Ambiguous column name "RelocateID" and "Ambiguous column name "RemovalistNumber" I don't know what is it trying to tell me here and couldn't find much help.
Regards and thanks
View 8 Replies
View Related
May 9, 2008
i do have my 'Product' TABLE IN DATABASE 'ABC'
Product TABLE OUTPUT
PRODUCT_CODE PRODUCT_TYPE PRODUCT_DESCPRODUCT_ID PRODUCT_GROUP_CODE
6001 computer NULL ENVD14
6002 keyboard NULL ENVD14
6003 mouse NULL ENVD14
6004 cables NULL ENVD14
6005 processor NULL ENVD14
AND 'Product_Mst' TABLE IN DATABASE 'XYZ'
Product_Mst OUTPUT
PROD_CODE Prod_Ver PROD_TYPE PROD_DESC PROD_ID PROD_GRP_CODE
6001 0 computer NULL ENVD 14
6002 0 keyboard NULL ENVD 14
6003 0 mouse NULL ENVD 14
6004 0 cables NULL ENVD 14
6005 0 processor NULL ENVD 14
Now i want TO CREATE TRIGGER such that every updation in Product TABLE will UPDATE the appropriate record IN Product_Mst
FOR example IF i fire below query IN ABC Database
UPDATE Product
SET PRODUCT_DESC = 'Available' WHERE Product_Code = 6001
Then the OUTPUT OF the Product_Mst shoub be..
Product_Mst OUTPUT
PROD_CODE Prod_Ver PROD_TYPE PROD_DESC PROD_ID PROD_GRP_CODE
6001 0 computer NULL ENVD 14
6001 1 computer NULL ENVD 14
6002 0 keyboard NULL ENVD 14
6003 0 mouse NULL ENVD 14
6004 0 cables NULL ENVD 14
6005 0 processor NULL ENVD 14
Means i want to increment the version by 1 and Insert that records into Product_Mst Table at every updation.
I hope i am clear with my question.
Regards
Prashant Hirani
View 1 Replies
View Related
Jan 6, 2015
We have Tables like parent and Child Table.Like we have Child Table as Name AcademyContacts,In that we have Columns like
Guid(PK)Not Null,
AcademyId(FK), Not Null,
Name,Null
WorkPhone,Null
CellPhone,Null
Email Id,Null
Other.Null
Since we have given Null to ''Workphone'',''Cellphone '', ''Email ID''.when inserting the data into these table.if the particular columns are empty while inserting also the data will get populate into the table.And.I need is if these columns are ''Workphone'',''Cellphone'' , ''Email ID'' they cant insert the data into table.
Like it must trigger like ''Please enter atleast one of these ''Workphone'',''Cellphone'' , ''Email ID'' columns.
View 4 Replies
View Related
Jul 20, 2005
Hi everybody,How can I Update a field from another table by Trigger? Can someone sendme the statment to do it?I have a table called Clients with fields : ID_Clients, ClientAnd Another called Doc with fields : ID_Doc, ID_Clients, ClientThese tables are in different databases and I would like to esure theintegrity by add a Trigger to update in Doc´s table the field Clienteverytime it´s changed in the Client´s table.Thanks for Attetion.Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jul 20, 2005
Hii have 2 Tablefirst one : Customerwith 4 Fields : cst_no,cst_name,total_Debit,tot_creditsecond one : Transactionwith 5 Fields : Trns_no,Trns_Date,cst_no,debit,creditMY QUESTION:HOW TO CREATE TRIGGER FOR UPDATE TOT_DEBIT AND TOT_CREDIT FILEDS INCUSTOMER TABLE FROM Transaction TABLEThank you
View 4 Replies
View Related
Jan 10, 2008
Hi folks,
I have created a trigger that I can enter and works great from the sqlcmd terminal. When I try to submit the same query in a .NET project I get an error that "Create Trigger must be the first statement in a batch". The Create trigger is submitted by itself through the sqlcommand.executenonquery() method.
I am trying to create a database for a project but the only thing that I can't seem to get working is submitting this trigger.
Appreciate any help.
Dave
View 5 Replies
View Related
May 10, 2008
I am trying to create a trigger to help me get a the time duration but this is not working.
Code Snippet
CREATE TRIGGER Duratn
ON dbo.CONTACTS
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
UPDATE dbo.CONTACTS
SET Duratn = (DATEDIFF(CallStartTime) - DATEDIFF(CallFinishTime))
My table is as follows:
No Caller CallStartTime CallFinishTime Duratn
10000 John 10/05/2008 18:13:00 10/05/2008 18:14:00 NULL
View 13 Replies
View Related
Oct 4, 2007
What I was trying to use to create the trigger was the same code I would use on Sql Server Express:
cmd.CommandText = "CREATE Trigger [contactsLastUpdate] on [contacts] for Insert, Update " +
"AS " +
"Begin " +
"SET NOCOUNT ON " +
"Update t " +
"set syncLastUpdate = GetDate() " +
"From contacts t " +
"Join inserted i " +
"On t.id = i.id " +
"SET NOCOUNT OFF " +
"End"; +
But I get an error message:
"There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = Trigger ]"
How do you guys create Triggers on SQL Server CE (2005)?
Thanks
View 3 Replies
View Related
Jun 18, 2007
Hello All!
I am trying to create a trigger that upon insertion into my table an email will be sent to that that recipeinent with a image attached ( like a coupon)That comes from a different table, problem is, It will not allow me to send the email ( using xp_sendmail) with the coupon attached. I am using varbinary for the coupon and nvarchar for the rest to be sent, I get an error that Invaild operator for data type. operator equals add, type equals varchar.
Looks basically like this(This is my test tables):
CREATE TRIGGER EileenTest ON OrgCouponTestMainFOR InsertAS declare @emailaddress varchar(50)declare @body varchar(300)declare @fname varchar(50)declare @coupon varbinary(4000)
if update(emailaddress)begin
Select @emailaddress=(select EmailAddress from OrgCouponTestMain as str), @fname=(select EmailAddress from OrgCouponTestMain as str) @Coupon=(select OrgCoupon1 from OrgCouponTest2 as image)
SET @body= 'Thank you' +' '+ @fname +' '+ ',Here is the coupon you requested' +' ' + @couponexec master.dbo.xp_sendmail @recipients = @emailaddress, @subject = 'Coupon', @message = @bodyEND
View 6 Replies
View Related
Oct 21, 2004
I have the following
CREATE TRIGGER dbo.tgrCacheCustomers
ON dbo.Customers
FOR INSERT, UPDATE, DELETE
AS
EXEC sp_makewebtask 'C:DependencyFile.txt','SELECT top 1 CustomerId FROM customers'
and I get the following error that I dont understand:
Error 21037: [SQL-DMO] The name specified in the Text property's 'CREATE ...' statement must match the Name property, and must be followed by valid TSQL statements.
Any ideas someone?
View 2 Replies
View Related
Aug 16, 2000
Hi there,
Once in a while, I find down there are some missing objects in production database. Because we share the sa password with couple of developers, therefore, it's hard find down who did it. So, I try to create a trigger in sysobjects table to prevent this problem, however, I keep getting the error message "error 229: create trigger permission denied on object 'sysobjects'"
although I log in using sa. Can someone give me some suggestions how to prevent this from happening beside using trace profiler and also why do I get the denied message when create trigger on sysobject even with sa login.
Thanks in advance
View 1 Replies
View Related
Nov 2, 2004
I need to create a simple audit trigger for a table with 12 columns. I need to determine which row was changed. Is there a simple way to do that. The table structure is
ID Integer(4)
barcode(25)
epc_tag(24)
bc_stop_flag(1)
reject_flag(1)
complete_flag(1)
hold_flag(1)
pe_1_flag
pe_2_flag
pe_3_flag
pe_4_flag
pe_5_flag
View 3 Replies
View Related
Sep 25, 2006
how can i Create a trigger to check if a value is NULL or = 0
i am trying :
CREATE TRIGGER [TR_User]
ON [User]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM User WHERE (Category = 0 OR Category IS NULL)
END
but in that way i dont CHECK the new inserted value directly, is it possible not to INSERT it if the value is NULL or = 0 ?
not to look all the table for each new line inserted
View 14 Replies
View Related
May 8, 2008
Hi,
I have a table with somefields, here i will only mention on which i need to perform an action, possibly with the use of Trigger.
Fields = Active, inactiveDate
Active Field is of bit datatype mean conatins 1 or 0,
1 means the user is active, but when i change the active field to 0, and make the user inactive i want the date to be populated automatically to inactiveDate field when active field is changed to 0.
any help is much appreciated
NAUMAN
View 2 Replies
View Related
May 31, 2008
Hi all
I've Created a Trigger statement and tried to execute this using ExecuteNonQuery.but it shows the following error
Incorrect syntax near 'GO'.
'CREATE TRIGGER' must be the first statement in a query batch.
if i start with Create Trigger statement it show "Incorrect Syntax near Create Trigger".
the following is the trigger statement which i've generated in C#
Can anyone help me?
thanks in advance
sri
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SampleTrigger' AND type = 'TR')
DROP TRIGGER SampleTrigger
GO
CREATE TRIGGER SampleTrigger
ON dbo.sample
AFTER INSERT
AS begin
SET NOCOUNT ON
DECLARE @RID as int
DECLARE @email AS nvarchar(50)
SELECT @email= i.email from inserted i
DECLARE @Name AS nvarchar(50)
SELECT @Name= i.Name from inserted i
DECLARE @Address AS nvarchar(50)
SELECT @Address= i.Address from inserted i
insert into Register(ServerName,DatabaseName,TableName) values('Sample','SamDatabase','SamTable')
SELECT @RID = @@Identity
insert into TableFields(RID,FieldName,FieldValue) values(@RID ,'Name',@Name)
insert into TableFields(RID,FieldName,FieldValue) values(@RID ,'Address',@Address)
insert into TableFields(RID,FieldName,FieldValue) values(@RID ,'email',@email)
end
View 1 Replies
View Related
Jul 6, 2006
I created and successfully tested a trigger on a test database. Now that Iwant to put this on a production system, the create trigger statement takesway too long to complete. I cancelled after a few minutes. The testtrigger took just a second to create. The test and production databases areidentical in design. Only difference is that there are users in theproduction system.Any ideas?Thanks
View 2 Replies
View Related
Jul 20, 2005
Is there a way to create a text file (such as a Windows Notepad file)by using a trigger on a table? What I want to do is to send a row ofinformation to a table where the table: tblFileData has only onecolumn: txtOutputI want to use the DB front end (MS Access) to send the text string tothe SQL backend, then have the SQL Server create a file to a path,such as F:/myfiledate.txt that holds the text in txtOutput, then thetrigger deletes the row in tblFileData.Can this be done easily?Any help is appreciated
View 9 Replies
View Related
Aug 17, 2007
Hi,
I have the following situation.
I have one table named as "Order", and other table as "Shipment". Now, I want to check if one column name "Status" in the "Örder" table has been changed (when it becomes status = 7) then I need to insert records in "Shipment" table.
Order Table:
OID===Order_Product===Order_Description===Order_Status
Shipment Table:
SID===Shipment_Description===DateTime===Status
I tried to create a trigger, but it is not working properly;
Create Trigger trg_InsertShipment ON [dbo].[Order]
FOR Update
AS
INSERT INTO Shipment
(
SID,
Shipment_Description,
DateTime,
Status
)
Select
'001' as SID,
'Nothing' as Shipment_Description,
'01/01/2007' as DateTime,
'Ready' as Status
WHERE Order.Status = 7
===============================================================
But it inserts the records into Shipment Table every time the status field or any other field in the Order Table is changed. What I want is that, "When Status Column in Order Table is Updated to 7, then insert record in Shipment Table".
How can I do that???
Thanks in advance...
View 5 Replies
View Related
May 22, 2006
Hi,
just started to write my first trigger for a view. Of course I got some errors, which I could could resolve except one.
Whenever I run my script I do get the following message:
Msg 213, Level 16, State 1, Procedure IO_Trig_INS_Zuordnung_Alles, Line 11
Insert Error: Column name or number of supplied values does not match table definition.
The Code where the error occurs according that message is the following:
CREATE TRIGGER IO_Trig_INS_Zuordnung_Alles ON Zuordnung_Alles
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Zuordnung. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT Z.[Anlagen-Nr_Z]
FROM Zuordnung Z, inserted I
WHERE Z.[Anlagen-Nr_Z] = I.[Anlagen-Nr_Z]))
INSERT INTO Zuordnung
SELECT [Anlagen-Nr_Z], [I-Nr], [an_A-Nr], [APS-Reg], [KSt des Inventars], [Gelände_Raum], [Servicenummer], [S-Nummer], [Hostname], [LOGIN-Name], [Mitarbeiter von], [Kategorie], [Verwendung], [Hersteller und Typ], [Ausstattung], [F-Nr], [Board], [Bios], [Prozessor], [Cache], [RAM], [SCSI-Contr], [CD-Rom], [Festplatten], [Wechselplatten], [Sonderausstattung], [Graphik], [Sound], [MPI], [NW-Karte], [MAC-Adr], [DHCP], [IP-Adr], [Netz], [Port], [Segment_ID], [NAP], [NW-Karte_2], [MAC-Adr_2], [DHCP_2], [IP-Adr_2], [Netz_2], [Port_2], [Segment_ID_2], [NAP_2], [Bemerkungen], [Betriebssysteme], [Dual-Boot], [geplante Maßnahmen], [Servicearbeiten], [aktualisiert], [wieder frei], [zurück von], [COB-Kostentyp], [COB-Import], [Dummy3], [Dummy4], [Inventursuche
FROM inserted
ELSE...
I did check on the columns serveral times, also I wrote them back with vba and used that but nothing helps. I would appreciate any help on possible errors in that code.
View 9 Replies
View Related
Mar 19, 2008
Hi,
I need help in creating a trigger before delete. The trigger should be in such a way that it should display a message, if there is any relationship with other table.
For example I have a table with employee details which have empid as primary key. I have another table with employee salary details where empid is foreign key. The trigger should check the relationship with these two tables. If I try to delete an emploeyee from employee details table and if there is a relationship of that employee with the salary table then the trigger should print a message. If there is no relationship then the trigger should perform the deletion.
I want to create a trigger like this.
Please help!!!!!!!!!!!!!!!!
View 6 Replies
View Related
Aug 2, 2007
Hi -
I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.
Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.
Is there a way to do this with a trigger (or an otherwise elegant way)?
Thanks in advance for ANY help or ideas.
Oliver
View 3 Replies
View Related
Feb 12, 2004
Hi,
I'm getting this error when I try to use "inserted" table in my create trigger call.
---------------------------
Microsoft Development Environment
---------------------------
ADO error: The column prefix 'inserted' does not match with a table name or alias name used in the query.
---------------------------
OK Help
---------------------------
Could you please, help me?
Thanks,
Rovshan
View 2 Replies
View Related
