I'm using SQL 2000, which is integrated with a VB.NET 2003 app.
I have an Audit trigger that logs changes to tables. I want to apply
this trigger to many different tables. It's the same trigger, with the
exception of the table name. I could manually change the table name in
the trigger and create it, over and over, but I'd like to automate
this - by iterating through the collection of tables and passing the
tablename to something that would create the audit trigger for that
table.
Any ideas? With any scripting language I can create the text of the
CREATE TRIGGER procedure for each table and use simple text
replacement, but how do I automate that inside of SQL?
We are using SQL Server 2014 and SSDT-BI 2013. We have a reporting environment where business users create objects which need to be persisted for fiscal year reporting. Let's say for instance SQLSERVER1SRVR1 they create table objects like below in the reporting environment.
Accounting2014, Accounting2015 in AccountingDB; Sales2014, Sales2015 in SalesDB; Products2014, Products2015 in ProductsDB; Inventory2014, Inventory2015 in InventoryDB etc....
These tables are persisted for auditing in a different environment SQLSERVER2SRVR2 for finance & audit folks.We would want to automate this process using SSIS to create tables in corresponding database and load data. I tried using For Each Loop container but the catch is I could loop the source or destination but how do we loop on Source & Destination at the same time (i.e when source is in AccountingDB destination to be AccountingDB, source SalesDB then destination SalesDB so on etc....
I am trying to create a trigger, that sets default values for all clients except one specific client in my table, however, the problem that i'm having is that when a member gets inserted into the database then the MemberID that gets created for that member in the Members table must also be inserted into the MemberUsergroup table
Here is the code that i have so far, It seems to work perfectly for the members table but because of the Foreign key constraint on the MemberUsergroup table it obviously doesn't work perfectly yet.
Any Help will be greatly appreciated.
Code Snippet ALTER Trigger [csp_MemberInsertRegister] ON [dbo].[Members] FOR INSERT AS UPDATE Members SET Registered = 1 where clientid <> 94 AND MemberID = @@Identity
I need to keep track of changes (delete/modify) in sysusers table. SQL Books Online mentionend that SQL Server 7.0 doesn't support trigger on system tables. What options do I have?
I am new to T-SQL and triggers Any help will be appreciated
I am trying to change this code to insert firstname, surname (taken from employee table on db A) to firstname, surname on customer table of DB B but also create cust_id on customer table and DB B. currently I am getting all rows of customer.cust_id filled with the same data whenever a new data is inserted into (firstname,surmname of employee table)
Create trigger gen_cust_id ON employee for insert AS Update customer SET cust_id =( SELECT Replicate('0',(4-DATALENGTH(CONVERT(varchar(10),i.id)))) + Convert (varchar(10),i.id) + Substring(i.lastname,1,3) + Substring(i.firstname,1,1) from employee C INNER JOIN inserted i on i.id=c.id) from employee C INNER JOIN inserted i on i.id=c.id
I have two databases one running of MS SQL Server and other on Oracle.. My requirement is such that, I want an trigger to be written on table in MS SQL Server such that it inserts / updates values of tables in Oracle Database tooo..is there any methodology for the same..
I have written a trigger that's supposed to go out and deletecorresponding records from multiple tables once I delete a specificrecord from a table called tblAdmissions.This does not work and I'm not sure why...Here's the code that's supposed to run, let's say, if a user (via a VB6.0 interface) decides to delete a record. If the record in thetblAdmissions table has the primary key (AdmissionID) of "123", thenthe code below is supposed to search other tables that have relatedinformation in them and also have an AdmissionID of "123" and deletethat information as well.Any ideas? Here's the code:CREATE TRIGGER tr_DeleteAdmissionRelatedInfo-- and here is the table nameON tblAdmissions-- the operation type goes hereFOR DELETEAS-- I just need one variable this timeDECLARE @AdmissionID int-- Now I'll make use of the deleted virtual tableSELECT @AdmissionID = (SELECT @AdmissionID FROM Deleted)-- And now I'll use that value to delete the data in-- the tblASIFollowUp TableDELETE FROM tblASIFollowUpWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblProgramDischarge TableDELETE FROM tblProgramDischargeWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblRoomAssignment TableDELETE FROM tblRoomAssignmentWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblTOADS TableDELETE FROM tblTOADSWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblUnitedWaySurvey TableDELETE FROM tblUnitedWaySurveyWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblWFGMSurvey TableDELETE FROM tblWFGMSurveyWHERE AdmissionID = @AdmissionID
I dont know what I am doing wrong. The trigger (see below) is doing what I want it to do when I do this:
INSERT INTO dbo.personal
(personal_id, chef_id,fornamn, efternamn)
VALUES
(40, 100, 'Malin', 'Markusson' , 'Boss')
but when I remove one value, the result in the logtable is NULL:
INSERT INTO dbo.personal (personal_id, chef_id,fornamn, efternamn)
VALUES
(40, 100, 'Malin', 'Markusson' )
How can I change the trigger so that it will give me the information of the values that have been updated, inserted or deleted when I dontchange all values (just a couple of them)?
My trigger: CREATE Trigger trigex
ON dbo.personal
FOR insert, update,delete
AS
INSERT INTO logtable (Innan_värde, Ny_värde)
SELECT
rtrim(cast(d.personal_id as varchar)+', '+cast(d.chef_id as varchar)+', '+rtrim(d.efternamn)+', '+ rtrim(d.fornamn)+ ', '+ rtrim(d.titel)),
(cast(i.personal_id as varchar)+', '+cast(i.chef_id as varchar)+', '+rtrim(i.efternamn)+', '+ rtrim(i.fornamn)+ ' '+ rtrim(i.titel))
FROM inserted i full join deleted d on i.personal_id = d.personal_id
I would like to create the trigger on tblCaseBillingDetails so that when the data in the Defendant fields are updated, the trigger fires and updates the Defendant count DefCount.
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
I created a view that joins several tables together and added an instead of insert, update trigger on it. My trigger works when I do an update/insert directly to the view, but it doesn't seem to fire when an insert/update is made to any of the tables that the view is based on. Is there a way to have the trigger fire when something happens to the tables that the view is based on? The application that the database is using is something that was purchased so I don't have access to the code to change any SQL statements that it's using.
We have DTS package automatically pouring data into the publishing database(source tables). During this process, we want to temporary disable certain triggers. However, the command
Alter table 'tbl' disable trigger 'abc' errored out. The error message said:
''Cannot alter the table 'tbl' because it is being published for replication."
I've digged more into this and found although it's not allowed to disable a triggers,
the SQLServer do allow delete the trigger and recreate them.
Is there any way to disable the trigger directly?
Thanks in advance,
Don
BTW:
I've used the following sql directly, however the trigger still fires.
UPDATE sysobjects SET status = status|2048 WHERE type = 'TR' AND parent_obj = OBJECT_ID (@table_name)
The only other way around now is to create stored procedures that dynamically create the trigger. Because our trigger is normmally larger than 8000 bytes. We have to create one stored procedure per trigger. This option is not acceptable because not only it takes quite a time, but also a maintainance nightmare.
Is it possible to execute a DTS package via a stored procedure?
We have created a web-based application with a SQL back-end, I would like the user to select an option on the .asp page that will then trigger the DTS. Is this possible? Any suggestions would be greatly appreciated.
I want to create a form (Infopath) or web page where the user can request for a new database I want the user to fill in the required info (like the size, name columns.....) and ones they're done filling the required info, they will click on a button and that should trigger the database automation script to run and if the database with the requested name is not created, the script should create a database for the user.
I want to automate a query that I am running everyday. So, what I want to do is run it at a particular time of the day say 11:00pm. Is this possible? Should I create a stored procedure? And also, I want to run an update statement on a particular table on the first of every month. If anyone can give me some advice on this, I would appreciate it.
hello people!I am very new to the SQL World. Currently I work on DB2 and ORACLE andthis is a new "fruit" for me.I have the task to automate the following scenario:- backup database A- restore database A into database B (overwrite database B)- perform the above every night at 23:00.Can anyone indicate me where do i find info how to perform the above?I know that you can do it through the Enterprise Manager but I need toscript the above (probably).Thanks
I have created the package using Import data ( from comma delimeted text file)
Actually I schedule the JOB to run the package from SQL Server Agent
I am getting error when start the job. ( job started successfully and executing time am getting error.)
here is the Log error
[298] SQLServer Error: 3621, The statement has been terminated. [SQLSTATE 01000] (ConnIsLoginSysAdmin)
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'VGOSVRSaleem', error code 0x2095. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
If I run the .dtsx file from outside. it works fine
do I need to do any kind of setup before running/starting the job ??
I am running this procedure to copy the call data info from oma11wicacct01 to oma11csql5tst01 and to vsql4shared
INSERT INTO [oma11csql5tst01].[rms sql].dbo.WICCallData ( ClientID, ProgramID, Calls, ActSeconds, UsageDate ) SELECT WICCallData.ClientID, WICCallData.ProgramID, Sum(WICCallData.Calls) AS SumOfCalls, Sum(WICCallData.ActSeconds) AS SumOfActSeconds, WICCallData.UsageDate FROM WICCallData GROUP BY WICCallData.ClientID, WICCallData.ProgramID, WICCallData.UsageDate HAVING (Sum(WICCallData.Calls)>0 or Sum(WICCallData.ActSeconds)>0) AND WICCallData.UsageDate>'2/3/2008'
The usage date has to be greater than the max date on the target database!
i already have a program that when you click a button it will get the new transactions in the database then pass it to another database now i need to automate the passing... once there is a new entry in the database it should automatically pass it to the other database. how could i do this?!
Is there a way to automate a sql server restore? I have a db that I need to include in an installation. I could use generate script but there is info in the db that needs to be copied too. What is the best way to do this? Thanx weisenbr
Using SQL Server 7.0, I need to watch for a file to be placed in a directory and then load it automatically. What is the best way to do this? I have the Bulk Insert process set up in DTS and would like to just add another process if possible.
Hi, and thanks for your help. I usually generate script of a database table, triggers, views, and store procedures by right click table/Task manager/selecting Generate sql script...etc
How can I automate this process, Is there a way where I can set up such sql scripts generation via Ms sql Agent so every week I can generate database script.
Dear All Im not the greatest SQL guy in the world...(as you will gather from this post)
I have multiple machines (x80) all with SQLExpress installed and a Database instance (MYDB).
This database is part of a demonstration software install. (which is limited).
I need to run a restore from a .BAK file to reset this software to the original version of the database. But this restore MUST happen without user interaction (Users are 17 yo students who know less SQL than me !!!)
Ive tried all the norms..
OSQL couldnt get it to connect at all. SQL query (Works but requires interaction)
